Find Column dependency

Viewed 56182

How to find objects which depend on particular column of table.

Ex:

Table: SomeTable

Cols: col1 pk, col2, col3

I want to find all the objects which are dependent on col1 (Pk)

9 Answers

It is working 100% fine. Try to use this. It will gives you extra detail. Thanks

SELECT
FK.TABLE_NAME AS Key_Table,
CU.COLUMN_NAME AS Foreignkey_Column,
PK.TABLE_NAME AS Primarykey_Table,
PT.COLUMN_NAME AS Primarykey_Column,
C.CONSTRAINT_NAME AS Constraint_Name
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
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
  ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT
  ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = 'HRM_tbEmployee'
AND PT.COLUMN_NAME = 'EmployeeID';
Related