Tuesday, December 8, 2009

How to find the Particular Table dependent Table List in Sql Server?

SELECT
   pt.COLUMN_NAME AS PrimaryField,
   c.CONSTRAINT_NAME AS KeyName,
   fk.TABLE_NAME AS ForeignTable,
   cu.COLUMN_NAME AS ForeignField
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 = 'MyTableName'
ORDER BY foreignTable ASC;

No comments:

 
Feedback Form