Voici quelques exemples d’utilisation des vues systèmes de MS SQL Server (depuis la version 2005)
-- quelles sont les tables dont la clef est composée d'une seule colonne
SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME
HAVING COUNT(*) = 1
-- quelles sont les tables dont la clef est composée d'une seule colonne
-- de type entier ?
SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME
HAVING COUNT(*) = 1
INTERSECT
SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C
ON KCU.TABLE_NAME = C.TABLE_NAME
AND KCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND KCU.COLUMN_NAME = C.COLUMN_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND C.DATA_TYPE IN ('int', 'bigint', 'smallint', 'tiniyint')
-- quelles sont les tables dont la clef est composée d'une seule colonne
-- de type entier ou NUMERIC, DECIMAL auto incrémenté ?
SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME
HAVING COUNT(*) = 1
INTERSECT
SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C
ON KCU.TABLE_NAME = C.TABLE_NAME
AND KCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND KCU.COLUMN_NAME = C.COLUMN_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND C.DATA_TYPE IN ('int', 'bigint', 'smallint', 'tiniyint', 'decimal', 'numeric')
INTERSECT
SELECT c.name, t.name
FROM sys.objects AS t
INNER JOIN sys.schemas AS c
ON t.schema_id = c.schema_id
INNER JOIN sys.identity_columns AS ic
ON t.object_id = ic.object_id
— quelles sont les tables pourvue d’une colonne auto incrémentée (IDENTITY)
SELECT c.name, t.name
FROM sys.objects AS t
INNER JOIN sys.schemas AS c
ON t.schema_id = c.schema_id
INNER JOIN sys.identity_columns AS ic
ON t.object_id = ic.object_id
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
