我想要一种方法来确定给定数据库中的哪些列通过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).