如何从SQL Server信息架构（元数据）获取依赖表？

| 我需要知道哪些表中的哪些列是主键以及外键，以便知道哪些表是相关的表（关联/许多关系）。我从这样的查询开始，但没有给我我需要的东西。 
  --get all tables with primary keys with pk,fk in it. --not complete
SELECT
TC.TABLE_NAME, TC.CONSTRAINT_TYPE, CCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC left JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON TC.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON CCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG AND
CCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
我需要对其进行更改，以便获得pk + fk列的表名和列名。帮助将不胜感激。     
2019-12-18 2 条评论
分享

没有找到相关结果

    已邀请:

    郸身

    从这里：http://wiki.lessthandot.com/index.php/Find_all_Primary_and_Foreign_Keys_In_A_Database 
    SELECT TC.CONSTRAINT_SCHEMA + \'.\'+ TC.TABLE_NAME AS PRIMARYKEYTABLE
,TC.CONSTRAINT_NAME AS PRIMARYKEY,
CU.COLUMN_NAME AS [PRIMARY COLUMN Name]
,COALESCE(RC1.CONSTRAINT_NAME,\'N/A\') AS FOREIGNKEY,
COALESCE(CFU.Column_Name, \'N/A\') AS [FOREIGN COLUMN Name]
,CASE WHEN TC2.TABLE_NAME IS NULL THEN \'N/A\'
 ELSE TC.CONSTRAINT_SCHEMA + \'.\' + TC2.TABLE_NAME  END AS FOREIGNKEYTABLE
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
 and TC.TABLE_SCHEMA = CU.TABLE_SCHEMA and Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1 ON TC.CONSTRAINT_NAME =RC1.UNIQUE_CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON TC2.CONSTRAINT_NAME =RC1.CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CFU ON RC1.CONSTRAINT_NAME  = CFU.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE =\'PRIMARY KEY\'
 ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_NAME,RC1.CONSTRAINT_NAME
        
    2019-12-18 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.043894052505493