Saturday, 9 May 2015

To Get list of cascade constraint with PK,FK table , column,constrain name in sql

OPTION 1
------


SELECT
ROW_NUMBER () OVER ( ORDER BY (SELECT 1)) AS RowId,
 pk.type as PK_TYPE,
SC.name AS PK_SCHEMA,
OBJECT_NAME(fk.referenced_object_id) AS PK_table,
COL_NAME(fk.referenced_object_id,fkc.referenced_column_id) PK_column,
pk.name AS PK_name,

fk.type as FK_TYPE,
SC.name AS FK_SCHEMA,
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action

INTO ADMIN.TblConstrainListBK

FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
    ON o1.object_id = fk.parent_object_id
 INNER JOIN SYS.schemas SC ON O1.schema_id=SC.schema_id  
 JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
    ON fkc.parent_object_id = c1.object_id
    AND fkc.parent_column_id = c1.column_id
JOIN sys.key_constraints pk
    ON fk.referenced_object_id = pk.parent_object_id
    AND fk.key_index_id = pk.unique_index_id
    AND SC.schema_id=PK.schema_id
   

OPTION 2
-------
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
fk.type as FK_TYPE,
pk.name AS PK_name,
pk.type as PK_TYPE,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
    ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
    ON fkc.parent_object_id = c1.object_id
    AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.key_constraints pk
    ON fk.referenced_object_id = pk.parent_object_id
    AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, fkc.constraint_column_id

No comments:

Post a Comment