SQL Server - view all foreign key dependencies

I want to find all of the db objects which are dependent on a given table, including other tables which reference the given table through foreign keys. I tried using "sp_depends" and it gives me the sprocs, views, and triggers but doesn't tell me what other table开发者_运维百科s have foreign keys to the given table. Any help?

select OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id)
    from sys.foreign_keys
    where referenced_object_id = object_id('SchemaName.TableName')

The key ingredient is the sys.foreign_keys view. I found a query that might be useful as an example in an article at mssqltips.com... Identify all of your foreign keys in a SQL Server database

In SSMS: right click your table and click "View Dependencies"

Also try this :

USE AUX; <- use your database name
GO
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
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.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
GO

and before the last GO you can add where OBJECT_NAME(f.parent_object_id) = 'your_table_name' to see only dependency for that table.

It will print TableName | Column name (FK) | Reference TableName | Reference Column Name