CODEWITHSUNDEEP
sqlsql-server
thenoob
thenoob

Reputation: 53

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 tables have foreign keys to the given table. Any help?

Upvotes: 5

Views: 3591

Answers (4)

pufos
pufos

Reputation: 2930

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

Upvotes: 1

Enull
Enull

Reputation: 256

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

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460108

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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

Upvotes: 9

Related Questions