post icon

Query to list Foreign Key and Update and Delete rule actions

Le tabelle di sistema di SQL Server tornano molto utili quando si vuole creare un elenco di chiavi esterne (Foreign Key) e relative regole di azione (Update and Delete rule).

La query che ci viene in aiuto per visualizzare tutte le chiavi esterne e a quali tabelle e colonne appartengono e relative regole di azione, è la seguente:

SELECT FK.TABLE_NAME AS FK_TABLE, CU.COLUMN_NAME AS FK_COLUMN, PK.TABLE_NAME AS PK_TABLE, PT.COLUMN_NAME AS PK_COLUMN, C.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME, C.UPDATE_RULE AS UPDATE_RULE, C.DELETE_RULE AS DELETE_RULE 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 T1.TABLE_NAME, T2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2 ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME WHERE T1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME

Enjoy snippet!

19 maggio 2009 @ 11:41

No comments yet.

Leave a comment

Leave a Reply

Are you human? Time limit is exhausted. Please reload CAPTCHA.