SQL Server 2008 - 获取表约束_数据库问题-跟版网

本文介绍了SQL Server 2008 - 获取表约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

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

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.

这是我目前所拥有的:

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]

推荐答案

您应该使用当前的 sys 目录视图(如果您使用的是 SQL Server 2005 或更高版本- sysobjects 视图已弃用，应避免使用)- 查看关于目录视图的大量 MSDN SQL Server 在线文档.

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 用于列的默认约束
sys.check_constraints 用于检查列的约束
sys.key_constraints 用于键约束(例如主键)
sys.foreign_keys 用于外键关系

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

还有更多 - 看看吧！

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

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 - 获取表约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！

SQL Server 2008 - 获取表约束

问题描述

推荐答案

相关文档推荐