Listing dependencies on database objects


You will get the different ways to write T-SQL to list the dependencies on Objects. Here I am summarizing all the ways we can go through…

1. via GUI

2. via T-SQL

SELECT      OBJECT_NAME(FKC.CONSTRAINT_OBJECT_ID) FKEYNAME,
            OBJECT_NAME(FKC.PARENT_OBJECT_ID) CHILD_ENTITY,
            C2.NAME CHILD_ATTRIBUTE,
            OBJECT_NAME(FKC.REFERENCED_OBJECT_ID) PARENT_ENTITY,
            C3.NAME PARENT_ATTRIBUTE
FROM  SYS.FOREIGN_KEY_COLUMNS FKC
JOIN  SYS.COLUMNS C2 ON FKC.PARENT_OBJECT_ID = C2.OBJECT_ID
            AND FKC.PARENT_COLUMN_ID = C2.COLUMN_ID
JOIN  SYS.COLUMNS C3 ON FKC.REFERENCED_OBJECT_ID = C3.OBJECT_ID
            AND FKC.REFERENCED_COLUMN_ID = C3.COLUMN_ID
ORDER BY CHILD_ENTITY
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s