我应该如何修改表达式以获取引用当前字段的引用表和字段

Question

我有那个表达式

  SELECT sysobjects.name AS TableName,  
  c.name AS ColumnName,  
  st.name AS TypeName,  
  "Length"  
  = case st.name  
  when 'uniqueidentifier'  then 'nvm'  
  when 'bit'  then 'nvm'  
  when 'int'  then 'nvm'
  when 'image'  then 'nvm'  
  when 'datetime'  then 'nvm'  
  when 'xml'  then 'nvm'  
  else (cast(c.length as varchar))  
  end,  
  case c.isnullable     
  when '0' then 'No'  
  when '1' then 'Yes' end AS 'Nullable'  
  FROM   
  dbo.syscolumns c  WITH (NOLOCK)   
  INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype   
  INNER JOIN dbo.sysobjects  WITH (NOLOCK) ON sysobjects.id=c.id   
  WHERE sysobjects.type in ('U')  
  ORDER BY TableName    

所以我得到这个输出：

  ACCREDITED_PROGRAMS   ID                  uniqueidentifier    nvm No  
  ACCREDITED_PROGRAMS   APPLICATION_FK          uniqueidentifier    nvm Yes  
  ACCREDITED_PROGRAMS   LICENCED_PROGRAM_FK uniqueidentifier    nvm Yes  

问题是让两个相邻的列指示哪些表和字段引用APPLICATION_FK、LICENCED_PROGRAM_FK和其他 FK

Answer 1

下面是您可以使用的示例：

SELECT o1.Name, c.Name, p.Name, rc.Name
FROM syscolumns c
INNER JOIN sysobjects o1 on o1.id = c.id --table
INNER JOIN sysobjects o2 on o2.parent_obj = o1.id and o2.type = 'F' --foreign key
LEFT JOIN sysreferences r on o2.id =  r.constid and c.colid = r.fkey1
LEFT JOIN sysobjects p on r.rkeyid = p.id
LEFT JOIN syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
WHERE o1.Name = 'TableName'

结果：

Address AddressID           NULL            NULL
Address AddressLine1        NULL            NULL
Address AddressLine2        NULL            NULL
Address City                NULL            NULL
Address StateProvinceID     StateProvince   StateProvinceID
Address PostalCode          NULL            NULL
Address SpatialLocation     NULL            NULL
Address rowguid             NULL            NULL
Address ModifiedDate        NULL            NULL