获取一个表内的外键字段名，以及外键字段名对应的外键表名

最新推荐文章于 2024-07-16 15:00:18 发布

zhanzkw

最新推荐文章于 2024-07-16 15:00:18 发布

阅读量2.3k

点赞数

分类专栏： SQL 文章标签： join null object

本文链接：https://blog.csdn.net/studyalllife/article/details/8085746

版权

SQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

select 字段序号,字段名,max(外键字段所在的表) as 外键字段所在的表,max(外键字段) as 外键字段,类型
from (SELECT distinct
  字段序号=a.colorder ,
  字段名=a.name ,
  外键字段所在的表=CASE WHEN tony.fkey is not null and tony.fkey=a.colid
  THEN object_name(tony.rkeyid) ELSE ''
  END ,
  外键字段=CASE WHEN tony.fkey is not null and tony.fkey=a.colid
  THEN (SELECT name FROM syscolumns
  WHERE colid=tony.fkey AND id=tony.fkeyid)   
  ELSE ''
  END,
  类型=b.name  
    
FROM sys.syscolumns a
  LEFT JOIN systypes b ON a.xtype = b.xusertype  
  INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0
  LEFT JOIN sysobjects htl ON htl.parent_obj=d.id AND htl.xtype='F'
  LEFT JOIN sysforeignkeys tony on htl.id=tony.constid
WHERE d.name='Articles' ) as TB
group by 字段序号,字段名,类型