SQL Server 2008-获取表约束

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]

5 个解决方案

#1

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

您应该使用当前系统编目视图(如果你在SQL Server 2005或更新——弃用,应该避免sysobjects视图),查看大量MSDN SQL Server书籍在线文档编目视图。

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

以下是一些你可能感兴趣的观点:

sys.default_constraints for default constraints on columns
用于列上的默认约束的sy . default_约束
sys.check_constraints for check constraints on columns
sys。对列的检查约束的check_constraints。
sys.key_constraints for key constraints (e.g. primary keys)
sys。密钥约束的key_constraints(例如主密钥)
sys.foreign_keys for foreign key relations
sys。外交钥匙关系

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

#2

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

#3

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

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

#5

I tried to edit the answer provided by marc_s however 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

#1