SQL Server 2008 - 获取表约束

SQL Server 2008- Get table constraints

提问by unos

Could you help me frame a query that retrieves the constraints in all the tables, the count of constraints in each table, and also display NULL for tables that do NOT have any constraints. Thx in advance!

你能帮我构建一个查询来检索所有表中的约束、每个表中的约束计数，并为没有任何约束的表显示 NULL。提前谢谢！

This is what I have so far:

这是我到目前为止：

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] 

回答by marc_s

You should use the current syscatalog views (if you're on SQL Server 2005or newer - the sysobjectsviews are deprecatedand should be avoided) - check out the extensive MSDN SQL Server Books Online documentation on catalog views here.

您应该使用当前的sys目录视图（如果您使用的是 SQL Server 2005或更高版本 - 这些sysobjects视图已被弃用，应该避免使用） -在此处查看有关目录视图的大量 MSDN SQL Server 联机丛书文档。

There are quite a few views you might be interested in:

有很多你可能感兴趣的视图：

• sys.default_constraintsfor default constraints on columns
• sys.check_constraintsfor check constraints on columns
• sys.key_constraintsfor key constraints (e.g. primary keys)
• sys.foreign_keysfor foreign key relations

• sys.default_constraints对于列的默认约束
• sys.check_constraints用于检查列的约束
• sys.key_constraints对于键约束（例如主键）
• sys.foreign_keys对于外键关系

and a lot more- check it out!

还有更多- 看看吧！

You can query and join those views to get the info needed - e.g. this will list the tables, columns and all default constraints defined on them:

您可以查询并加入这些视图以获取所需的信息 - 例如，这将列出表、列和在其上定义的所有默认约束：

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

回答by Jose Luis Bracamonte Amavizca

I used the following query to retrieve the information of constraints in the SQL Server 2012, and works perfectly. I hope it would be useful for you.

我使用以下查询检索SQL Server 2012中的约束信息，并且完美运行。我希望它对你有用。

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

回答by Proje

You Can Get With This Query

你可以得到这个查询

Unique Constraint,

唯一约束，

Default Constraint With Value,

带值的默认约束，

Foreign Key With referenced Table And Column

带有引用表和列的外键

And Primary Key Constraint.

和主键约束。

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');

回答by Thabang Mogano

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])
    )

回答by Matt Vukomanovic

I tried to edit the answerprovided by marc_showever it wasn't accepted for some reason. It formats the sql for easier reading, includes the schema and also names the Default name so that this can easily be pasted into other code.

我试图编辑由marc_s提供的答案，但由于某种原因没有被接受。它格式化 sql 以便于阅读，包括架构并命名默认名称，以便可以轻松地将其粘贴到其他代码中。

  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

回答by L. Tyler

Here's a script to get foreign keys:

这是一个获取外键的脚本：

    SELECT TOP(150)
       t.[name] AS [Table],
       cols.[name] AS [Column],
       t2.[name] AS [Referenced Table],
       c2.[name] AS [Referenced Column],
       constr.[name] AS [Constraint]
  FROM sys.tables t
 INNER JOIN sys.foreign_keys constr ON constr.parent_object_id = t.object_id
 INNER JOIN sys.tables t2 ON t2.object_id = constr.referenced_object_id
 INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = constr.object_id
 INNER JOIN sys.columns cols ON cols.object_id = fkc.parent_object_id AND cols.column_id = fkc.parent_column_id
 INNER JOIN sys.columns c2 ON c2.object_id = fkc.referenced_object_id AND c2.column_id = fkc.referenced_column_id
 --WHERE t.[name] IN ('?', '?', ...)
 ORDER BY t.[Name], cols.[name]