我想要一种方法来确定给定数据库中的哪些列通过PK / FK关系连接.我可以通过返回给定表的PK / FK信息
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).
