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)
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)
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';