12
mars
2012
FOREIGN KEYs non indexés
mars
2012
Un article de zinzineti
Il est souvent recommandé d’indexer les clés étrangères (FK). Cette opération d’indexation ne doit pas se faire de façon mécanique. Elle devrait passer par les phases d’analyse, de création et de test. Mais avant comment identifier les FKs non indexés ?
SELECT a.TABLE_SCHEMA,a.TABLE_NAME, b.COLUMN_NAME, b.CONSTRAINT_NAME,a.IS_DEFERRABLE,a.INITIALLY_DEFERRED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE a.CONSTRAINT_TYPE = 'FOREIGN KEY'
--AND a.TABLE_NAME = 'Commandes' /* pour une table specifique */
AND a.TABLE_NAME + '.' + b.COLUMN_NAME NOT IN (
SELECT o.name +'.'+c.name
FROM sys.objects o
INNER JOIN sys.indexes i ON i.object_id = o.object_id
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.index_columns ic ON ic.object_id = o.object_id AND i.index_id = ic.index_id AND ic.column_id = c.column_id
WHERE o.type in ('U')
)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE a.CONSTRAINT_TYPE = 'FOREIGN KEY'
--AND a.TABLE_NAME = 'Commandes' /* pour une table specifique */
AND a.TABLE_NAME + '.' + b.COLUMN_NAME NOT IN (
SELECT o.name +'.'+c.name
FROM sys.objects o
INNER JOIN sys.indexes i ON i.object_id = o.object_id
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.index_columns ic ON ic.object_id = o.object_id AND i.index_id = ic.index_id AND ic.column_id = c.column_id
WHERE o.type in ('U')
)
——————-
Etienne ZINZINDOHOUE