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屋!