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
------
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