Lister les contraintes et index uniques d'une base
Une requête unique pour lister les clés primaires, uniques, étrangères, CHECK et DEFAULT de chaque table et vue utilisateur.
La requête ci-dessous liste, pour chaque table et vue utilisateur de la base courante, les contraintes uniques et index uniques, clés primaires, clés étrangères, contraintes CHECK et contraintes DEFAULT, dans un seul jeu de résultats.
SELECT table_view,
object_type,
constraint_type,
constraint_name,
details
FROM (
SELECT schema_name(t.schema_id) + '.' + t.[name] AS table_view,
CASE WHEN t.[type] = 'U' THEN 'Table'
WHEN t.[type] = 'V' THEN 'View'
END AS [object_type],
CASE WHEN c.[type] = 'PK' THEN 'Primary key'
WHEN c.[type] = 'UQ' THEN 'Unique constraint'
WHEN i.[type] = 1 THEN 'Unique clustered index'
WHEN i.type = 2 THEN 'Unique index'
END AS constraint_type,
ISNULL(c.[name], i.[name]) AS constraint_name,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [details]
FROM sys.objects t
LEFT OUTER JOIN sys.indexes i
ON t.object_id = i.object_id
LEFT OUTER JOIN sys.key_constraints c
ON i.object_id = c.parent_object_id
AND i.index_id = c.unique_index_id
CROSS APPLY (
SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH('')
) D (column_names)
WHERE is_unique = 1
AND t.is_ms_shipped <> 1
UNION ALL
SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name AS foreign_table,
'Table',
'Foreign key',
fk.name AS fk_constraint_name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name
FROM sys.foreign_keys fk
INNER JOIN sys.tables fk_tab
ON fk_tab.object_id = fk.parent_object_id
INNER JOIN sys.tables pk_tab
ON pk_tab.object_id = fk.referenced_object_id
INNER JOIN sys.foreign_key_columns fk_cols
ON fk_cols.constraint_object_id = fk.object_id
UNION ALL
SELECT schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Check constraint',
con.[name] AS constraint_name,
con.[definition]
FROM sys.check_constraints con
LEFT OUTER JOIN sys.objects t
ON con.parent_object_id = t.object_id
LEFT OUTER JOIN sys.all_columns col
ON con.parent_column_id = col.column_id
AND con.parent_object_id = col.object_id
UNION ALL
SELECT schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition]
FROM sys.default_constraints con
LEFT OUTER JOIN sys.objects t
ON con.parent_object_id = t.object_id
LEFT OUTER JOIN sys.all_columns col
ON con.parent_column_id = col.column_id
AND con.parent_object_id = col.object_id
) a
ORDER BY table_view, constraint_type, constraint_name;
Remarques
- Pour une clé étrangère, la colonne
detailsindique seulement la table référencée, pas les colonnes. - Une clé étrangère composite renvoie une ligne par colonne, à cause de la jointure sur
sys.foreign_key_columns. - Les index uniques qui ne sont pas des contraintes apparaissent en
Unique indexouUnique clustered index.