MCITP

MCITP

Friday, September 30, 2011

Finding all dependent table on a given table in a database present in SQL Server


SELECT
pt.TABLE_NAME AS primarytable,
fk.TABLE_NAME AS dependent_table,
cu.COLUMN_NAME AS column_linking_the_two_tables,
c.CONSTRAINT_NAME AS keyName
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT tc.TABLE_NAME, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pt ON pt.TABLE_NAME = pk.TABLE_NAME
WHERE pk.TABLE_NAME = 'TableName_on_which_dependent_table_is_required'
ORDER BY dependent_table ASC;


No comments:

Post a Comment

Followers