SQLServer - How to find dependent tables on my table?

Using SQLServer :

I have a table user :

• id

• name

• email开发者_C百科

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

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

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

---

The way to get ONLY TABLE references (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:

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

---

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.

---

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

---

Using SSMS GUI:

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

Additionally If you want to do it with TSQL in where all objects that depends on your table

Approach-1: Using sp_depends store procedure , though sql server team is going to remove this feauture in future version but it still useful to get all the dependencies on the specified Object, includes Tables, Views, Stored Procedures, Constraints, etc., sql server team recommend to use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

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

Approach-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');

Approach-3: Find Table dependencies in Function, Procedure and View

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

Approach-4:

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

If you want to get all objects on which your table depends on.

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

---

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".

---

Try this

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

---

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 Users table, just add a WHERE clause before the final ORDER BY

WHERE PrimaryTable = 'Users'

---

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'