SQL Server 2008-获取表约束 [英] SQL Server 2008- Get table constraints

问题描述

您可以帮助我构建一个查询，检索所有表中的约束，每个表中的约束计数，以及对没有任何约束的表显示NULL。提前Thx！

这是我到目前为止：

 选择SysObjects。[Name] As [Constraint Name]，
 Tab。[Name] as [Table Name]，
 Col. [Name] As [Column Name] 
 From SysObjects Inner Join 
（选择[Name]，[ID] From SysObjects）As Tab 
在Tab。[ID] = Sysobjects。[Parent_Obj] 
内部联接sysconstraints在sysconstraints.Constid = Sysobjects。 ] 
 Inner Join SysColumns Col On Col. [ColID] = sysconstraints。[ColID] And Col. [ID] = Tab。[ID] 
 order by [Tab]。[Name] 
  c> 
 
 
解决方案
您应该使用当前的 sys 目录视图（如果您使用的是SQL Server  2005 或更新版本 -   sysobjects 视图已弃用并应避免） - 请参阅有关目录视图的详细MSDN SQL Server联机丛书文档 
 
 
 
您可能感兴趣的观点有很多：
 
 
 
 
 
  sys.default_constraints 用于列的默认约束
 
 
  sys.check_constraints 对列的约束
 
 
  sys.key_constraints 主键）
 
 
  sys.foreign_keys 用于外键关系
 
 
 
 
 
和很多  - 检查！
 
 
 
您可以查询和加入这些视图， - 例如这将列出表，列及其上定义的所有默认约束：
  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 
  
 
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!

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] 

解决方案
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.

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


sys.default_constraints for default constraints on columns
sys.check_constraints for check constraints on columns
sys.key_constraints for key constraints (e.g. primary keys)
sys.foreign_keys for foreign key relations


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


                        
这篇关于SQL Server 2008-获取表约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！