sql-server – 查找与给定主键关联的外键

SELECT *  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu 
WHERE EXISTS (
    SELECT tc.* 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'  
        AND tc.TABLE_NAME = 'MyTable'  
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

但对于从这样的查询返回的PK,我如何建立相关的FK(假设有一个)？

我知道您也可以通过以下方式获取引用的表：

SELECT CONSTRAINT_NAME = name,FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),FOREIGN_TABLE = OBJECT_NAME(parent_object_id),REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),REFERENCED_TABLE = OBJECT_NAME(referenced_object_id) 
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

但我现在正在努力获得明确的列引用.

我正在为QlikView创建一个脚本生成器.要生成脚本,我需要约束和相关链接.我需要任何给定列的所有约束信息(如果有的话).

我想构建一个数据库类,它包含给定数据库的所有信息.然后,将使用此类结构database.table.column.constraints来获取PK / FK上不同列之间的匹配.

显然,有些列只有FK,在这种情况下我也想检索相应密钥的PK信息;有些人只有PK,然后我想反过来.有些当然可以兼得.

解决方法

SELECT
    o1.name AS FK_table,c1.name AS FK_column,fk.name AS FK_name,o2.name AS PK_table,c2.name AS PK_column,pk.name AS PK_name,fk.delete_referential_action_desc AS Delete_Action,fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name,o2.name,fkc.constraint_column_id

输出有八列：外键的表名和列名(FK_table,FK_column),外键约束的名称(FK_name),引用的PK或唯一索引表和列名(PK_table,PK_column),引用的PK或唯一索引(PK_name)的名称,以及更新/删除级联操作(Delete_Action,Update_Action).

(已编辑添加更多输出列.)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!