由网友(淡忘 那段情…)分享简介:我如何可以找到所有的外键依赖于特定的列? How can I find all of the foreign key dependencies on a particular column? 有什么不同的选择(图形在SSMS中,查询/视图在SQL Server中,第三方数据库工具,code。在.NET)?What...
我如何可以找到所有的外键依赖于特定的列?
How can I find all of the foreign key dependencies on a particular column?
有什么不同的选择(图形在SSMS中,查询/视图在SQL Server中,第三方数据库工具,code。在.NET)?
What are the different alternatives (graphically in SSMS, queries/views in SQL Server, 3rd party database tools, code in .NET)?
推荐答案
下面的查询将有助于让你开始。它列出了所有的外键关系当前数据库中。
The following query will help to get you started. It lists all Foreign Key Relationships within the current database.
SELECT
K_Table = FK.TABLE_NAME,
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
您还可以查看数据库图表中以图形方式在SQL Server Management Studio中的关系。
You can also view relationships graphically within SQL Server Management studio within Database Diagrams.
相关推荐
最新文章