SQL Server 2008- Obtenir des contraintes de table

Question

Pourriez-vous m'aider à formuler une requête qui récupère les contraintes dans toutes les tables, le nombre de contraintes dans chaque table, et qui affiche également les données suivantes NULL pour les tables qui n'ont PAS de contraintes.

Voici ce que j'ai jusqu'à présent :

Select  SysObjects.[Name] As [Constraint Name] ,
        Tab.[Name] as [Table Name],
        Col.[Name] As [Column Name]
From SysObjects Inner Join 
(Select [Name],[ID] From SysObjects) As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj] 
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
order by [Tab].[Name]

Answer 1

Vous devez utiliser l'actuel sys vues de catalogue (si vous êtes sur SQL Server 2005 ou plus récent - le sysobjects les vues sont déprécié et à éviter) - consultez le une vaste documentation MSDN SQL Server Books Online sur les vues de catalogue ici .

Il y a plusieurs points de vue qui pourraient vous intéresser :

• sys.default_constraints pour les contraintes par défaut sur les colonnes
• sys.check_constraints pour les contraintes de contrôle sur les colonnes
• sys.key_constraints pour les contraintes de clés (par exemple, les clés primaires)
• sys.foreign_keys pour les relations clés étrangères

et un beaucoup plus - Regardez !

Vous pouvez interroger et joindre ces vues pour obtenir les informations dont vous avez besoin - par exemple, ceci donnera la liste des tables, des colonnes et de toutes les contraintes par défaut définies sur elles :

SELECT 
    TableName = t.Name,
    ColumnName = c.Name,
    dc.Name,
    dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
ORDER BY t.Name

Answer 2

Vous pouvez obtenir avec cette requête

Contrainte unique,

Contrainte par défaut avec valeur,

Clé étrangère avec table et colonne référencées

Et contrainte de clé primaire.

Select C.*, (Select definition From sys.default_constraints Where object_id = C.object_id) As dk_definition,
(Select definition From sys.check_constraints Where object_id = C.object_id) As ck_definition,
(Select name From sys.objects Where object_id = D.referenced_object_id) As fk_table,
(Select name From sys.columns Where column_id = D.parent_column_id And object_id = D.parent_object_id) As fk_col
From sys.objects As C
Left Join (Select * From sys.foreign_key_columns) As D On D.constraint_object_id = C.object_id 
Where C.parent_object_id = (Select object_id From sys.objects Where type = 'U'
And name = 'Table Name Here');

Answer 3

J'ai utilisé la requête suivante pour récupérer les informations des contraintes dans le serveur SQL 2012, et cela fonctionne parfaitement. J'espère qu'elle vous sera utile.

SELECT 
    tab.name AS [Table]
    ,tab.id AS [Table Id]
    ,constr.name AS [Constraint Name]
    ,constr.xtype AS [Constraint Type]
    ,CASE constr.xtype WHEN 'PK' THEN 'Primary Key' WHEN 'UQ' THEN 'Unique' ELSE '' END AS [Constraint Name]
    ,i.index_id AS [Index ID]
    ,ic.column_id AS [Column ID]
    ,clmns.name AS [Column Name]
    ,clmns.max_length AS [Column Max Length]
    ,clmns.precision AS [Column Precision]
    ,CASE WHEN clmns.is_nullable = 0 THEN 'NO' ELSE 'YES' END AS [Column Nullable]
    ,CASE WHEN clmns.is_identity = 0 THEN 'NO' ELSE 'YES' END AS [Column IS IDENTITY]
FROM SysObjects AS tab
INNER JOIN SysObjects AS constr ON(constr.parent_obj = tab.id AND constr.type = 'K')
INNER JOIN sys.indexes AS i ON( (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tab.id) AND i.name = constr.name )
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
                                    AND (ic.index_id=CAST(i.index_id AS int) 
                                    AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE tab.xtype = 'U'
ORDER BY tab.name

Answer 4

SELECT
    [oj].[name] [TableName],
    [ac].[name] [ColumnName],
    [dc].[name] [DefaultConstraintName],
    [dc].[definition]
FROM
    sys.default_constraints [dc],
    sys.all_objects [oj],
    sys.all_columns [ac]
WHERE
    (
        ([oj].[type] IN ('u')) AND
        ([oj].[object_id] = [dc].[parent_object_id]) AND
        ([oj].[object_id] = [ac].[object_id]) AND
        ([dc].[parent_column_id] = [ac].[column_id])
    )

Answer 5

J'ai essayé de modifier le réponse fourni par marc_s mais il n'a pas été accepté pour une raison quelconque. Il met en forme le sql pour une lecture plus facile, inclut le schéma et nomme également le nom de défaut afin qu'il puisse être facilement collé dans un autre code.

  SELECT SchemaName = s.Name,
         TableName = t.Name,
         ColumnName = c.Name,
         DefaultName = dc.Name,
         DefaultDefinition = dc.Definition
    FROM sys.schemas                s
    JOIN sys.tables                 t   on  t.schema_id          = s.schema_id
    JOIN sys.default_constraints    dc  on  dc.parent_object_id  = t.object_id 
    JOIN sys.columns                c   on  c.object_id          = dc.parent_object_id
                                        and c.column_id          = dc.parent_column_id
ORDER BY s.Name, t.Name, c.name