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

SQLServer - How to find dependent tables on my table?

提问by Yugal Jindle

Using SQLServer:

使用SQLServer：

I have a table user:

我有一张桌子user：

• id

• name

• email

• id

• name

• email

There are some other tables (about 200 more tables), some of which use user.idas foreign key on cascade delete.

还有一些其他表（大约 200 个以上的表），其中一些user.id用作cascade delete.

So, I want to find out - Which tables use this foreign key (user.id) ?

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

I am accessing my sql-server with SQL Server Management Studio.

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

回答by Nikita

The way to get ONLY TABLEreferences (i.e. tables that uses given table as a foreign key and tables that given table uses the same way) you can use this code snippet:

获取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'))

回答by Edwin de Koning

In SQL server management studio, you can right click your table in the object explorer, and then select 'View Dependencies'. This will open a new window in which you can see all other objects (not just tables) that depend on your table, and on which your table depends.

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

回答by rmycroft

Here is a stored procedure I put together based in part on the above answer.

这是我部分基于上述答案组合在一起的存储过程。

-- =============================================
-- 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

回答by Chris W

If you've got these defined as foreign keys then just examine the table design and look at the Relationships dialog which will show you everything that's defined for the table.

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

Alternatively you can use "View Dependencies".

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

回答by Chris W

Try this

尝试这个

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

回答by Leniency

Another option to get foreign keys.

获取外键的另一种选择。

-- 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]

This will yield all foreign keys and their primary/dependent information. It also includes an extra column if the dependent column is part of the primary key in the dependent table - sometimes important to note that.

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

To get only the Userstable, just add a WHEREclause before the final ORDER BY

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

WHERE PrimaryTable = 'Users'

回答by Lord Darth Vader

Option to get all tables with Schema Names

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

    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'