SQLServer - 如何查找我的表上的依赖表？

Question

使用 SQLServer ：

我有一个表 user ：

• id

• < code>name

• email

还有一些其他表（大约 200更多表），其中一些使用 user.id 作为外键在级联删除上。

所以，我想找出 - 哪些表使用这个外键（user.id）？

我正在使用 SQL Server Management Studio 访问我的 sql-server。

Answer 1

获取ONLY TABLE引用（即使用给定表作为外键的表和给定表使用相同方式的表）的方法可以使用以下代码片段：

declare @tableName varchar(64);
set @tableName = 'TABLE';

select
SO_P.name as [parent table]
,SC_P.name as [parent column]
,'is a foreign key of' as [direction]
,SO_R.name as [referenced table]
,SC_R.name as [referenced column]
,*
from sys.foreign_key_columns FKC
inner join sys.objects SO_P on SO_P.object_id = FKC.parent_object_id
inner join sys.columns SC_P on (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
inner join sys.objects SO_R on SO_R.object_id = FKC.referenced_object_id
inner join sys.columns SC_R on (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
where
    ((SO_P.name = @tableName) AND (SO_P.type = 'U'))
    OR
    ((SO_R.name = @tableName) AND (SO_R.type = 'U'))

Answer 2

在 SQL Server Management Studio 中，您可以右键单击对象资源管理器中的表，然后选择“查看依赖关系”。这将打开一个新窗口，您可以在其中看到依赖于您的表以及您的表所依赖的所有其他对象（不仅仅是表）。

Answer 3

这是我部分基于上述答案整理的存储过程。

-- =============================================
-- Author:      R. Mycroft
-- Create date: 2012-08-08
-- Description: Lists foreign keys to & from a named table.  
-- (Have yet to find this one via Google!)
-- =============================================
alter procedure usp_ListTableForeignKeys 
    @tableName varchar(300) = ''
as
begin
set nocount on;

select 
    object_name(parent_object_id) as childObjectName
    , object_name(referenced_object_id) as parentObjectName
    , name, type_desc, create_date
from sys.foreign_keys
where object_name(parent_object_id) = @tableName
or object_name(referenced_object_id) = @tableName
end

Answer 4

使用 SSMS GUI：

在 SQL Server Management Studio (SSMS) 中，您可以右键单击表并选择“查看依赖关系”。这将打开一个新窗口，您可以在其中看到依赖于您的表以及您的表也依赖于的所有对象。

另外，如果您想使用 TSQL 执行此操作，其中所有对象都依赖于您的表，

则方法 1：使用 sp_depends 存储过程，尽管 sql server 团队将在未来版本中删除此功能，但它仍然有用到获取指定对象的所有依赖关系，包括表、视图、存储过程、约束等，sql server 团队建议使用 sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 代替。

-- Query to find Table Dependencies in SQL Server: 
EXEC sp_depends @objname = N'dbo.aspnet_users' ;

方法 2：

-- Query to find Table Dependencies in SQL Server: 
    SELECT referencing_id, 
       referencing_schema_name, 
       referencing_entity_name 
FROM sys.dm_sql_referencing_entities('dbo.aspnet_users', 'OBJECT');

方法 3：在函数、过程和视图中查找表依赖

SELECT *
    FROM sys.sql_expression_dependencies A, sys.objects B
    WHERE referenced_id = OBJECT_ID(N'dbo.aspnet_users') AND 
        A.referencing_id = B.object_id

项 方法 4：

-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies N'dbo.aspnet_users', null, 1315327

如果您想获取表所依赖的所有对象。

-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies N'dbo.aspnet_users', null, 1053183 

Answer 5

如果您已将这些定义为外键，则只需检查表设计并查看“关系”对话框，该对话框将显示为表定义的所有内容。

或者，您可以使用“查看依赖关系”。

Answer 6

试试这个

select 
    OBJECT_NAME(parent_object_id) as parent_object_name,
    *
from sys.foreign_keys
where name = 'YourFKName'

Answer 7

获取外键的另一种选择。

-- CTE to fetch all primary key information.
WITH PrimaryKeys AS (
    SELECT 
        s.name as [Schema], 
        t.name as [Table], 
        c.name as [Column], 
        ic.index_column_id AS [ColumnNumber]
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
    JOIN sys.indexes i ON ic.object_id = i.object_id and ic.index_id = i.index_id
    JOIN sys.tables t ON i.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE i.is_primary_key = 1
),
-- CTE to fetch table information.
TableInfo AS (
    SELECT
        tab.name AS [Table],
        col.name AS [Column],
        sch.name AS [Schema],
        tab.object_id AS TableId,
        col.column_id AS ColumnId
    FROM sys.tables tab
    JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
    JOIN sys.columns col ON col.object_id = tab.object_id
)

-- Primary query selecting foreign keys and primary/dependent information.
SELECT
    obj.name AS FK_NAME,
    p.[Schema] AS [PrimarySchema],
    p.[Table] AS [PrimaryTable],
    p.[Column] AS [PrimaryColumn],
    d.[Schema] AS [DependentSchema],
    d.[Table] AS [DependentTable],
    d.[Column] AS [DependentColumn],
    prim.ColumnNumber AS IsDependentPrimaryColumn -- has value if is part of dependent table's primary key
FROM  sys.foreign_key_columns fkc
JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
JOIN TableInfo d ON d.TableId = fkc.parent_object_id AND d.ColumnId = fkc.parent_column_id
JOIN TableInfo p ON p.TableId = fkc.referenced_object_id AND p.ColumnId = fkc.referenced_column_id

-- Join in primary key information to determine if the dependent key is also
-- part of the dependent table's primary key.
LEFT JOIN PrimaryKeys prim ON prim.[Column] = d.[Column] AND prim.[Table] = d.[Table]

ORDER BY [PrimarySchema], [PrimaryTable], [DependentSchema], [DependentTable]

这将产生所有外键及其主要/依赖信息。如果从属列是从属表中主键的一部分，它还包括一个额外的列 - 有时需要注意这一点。

要仅获取 Users 表，只需在最终的ORDER BY 之前添加一个 WHERE 子句即可

WHERE PrimaryTable = 'Users'

Answer 8

获取具有架构名称的所有表的选项

    select
SO_P.name as [parent table]
,SS_P.name as [parent table schema]
,SC_P.name as [parent column]
,'is a foreign key of' as [direction]
,SO_R.name as [referenced table]
,SS_R.name as [referenced table schema]
,SC_R.name as [referenced column]
,*
from sys.foreign_key_columns FKC
inner join sys.objects SO_P on SO_P.object_id = FKC.parent_object_id
inner join sys.schemas SS_P on SS_P.schema_id = SO_P.schema_id
inner join sys.columns SC_P on (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
inner join sys.objects SO_R on SO_R.object_id = FKC.referenced_object_id
inner join sys.schemas SS_R on SS_R.schema_id = SO_P.schema_id
inner join sys.columns SC_R on (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)

where SO_P.type = 'U' OR SO_R.type = 'U'