Get a list of SQL Server constraints without redundancy
With this request I get a list of all the limitations of my database CHECK
, FOREIGN_KEY
, PRIMARY_KEY
and UNIQUE_KEY
.
SELECT
o.object_id as ID, o.name AS Name,
OBJECT_NAME(o.parent_object_id) AS TableName,
o.type_desc AS TypeName,
cs.COLUMN_NAME as ColumnName
FROM
sys.objects o
LEFT JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cs ON o.name = cs.CONSTRAINT_NAME
WHERE
o.type = 'C' or o.type = 'F' or o.type = 'PK' or o.type = 'UQ'
However, there are some with many "ColumnName" and I want to do it.
For example:
'PK_ENTITE_SIGN_DOSSIER_ID_DOSSIER_ID_ENTITE_ID_GROUPE_SIGN_ID_PERSONNE_ID_SCHEMA'
is PRIMARY_KEY
in the table ENTITE_SIGN_DOSSIER
and contains ID_DOSSIER
, ID_ENTITE
, ID_GROUPE_SIGN
, ID_PERSONNE
and ID_SCHEMA
(5 columns), and in this case my request returns 5 rows for this limitation.
How can I send the column name to the query result?
Many thanks for your help
source to share
This is the standard function trick xml
and stuff
:
SELECT o.object_id AS ID ,
o.name AS Name ,
OBJECT_NAME(o.parent_object_id) AS TableName ,
o.type_desc AS TypeName ,
ca.ColumnName
FROM sys.objects o
CROSS APPLY ( SELECT STUFF(( SELECT ', ' + cs.COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cs
WHERE o.name = cs.CONSTRAINT_NAME
FOR
XML PATH('')
), 1, 2, '') AS ColumnName
) ca
WHERE o.type = 'C'
OR o.type = 'F'
OR o.type = 'PK'
OR o.type = 'UQ'
ORDER BY ID
source to share