select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
解决方案 »
- 同一语句,在SQL2000中很快,为什么SQL2008中非常慢呢?
- 求一个联合查询语句
- 一个取记录集的问题(百分求解)
- 大家好,请教一下学习SQL Server看什么书比较合适呀?那位能推荐一下。
- 还原数据的一个小问题
- sqlserver导出txt文件的问题:Cannot use the output option when passing a constant to a stored procedure
- sql 2005 Analysis Services 连接不上。紧急!!
- 两个游标在嵌套的循环内怎样判断?
- 怎样让一个程序在机器启动后就运行,并且在任务栏中显示成类似SQL Server的小图标
- 求助!Win7 64位 装SQL SERVER 2012企业版为什么不行
- 想把 A数据库中的工号字段修改为与B数据库工号相同,两个库中姓名字段内存相同
- 分区表怎么设置主键,求指点,谢谢
FK_Column = CU.COLUMN_NAME ,
PK_Table = PK.TABLE_NAME ,
PK_Column = PT.COLUMN_NAME ,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN ( SELECT i1.TABLE_NAME ,
i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME--外键约束
SELECT OBJECT_NAME(f.parent_object_id) TableName ,
COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.referenced_object_id) = 'Application'
ORDER BY TableName