SQL Server 2008-获取表约束
SQL Server 2008-获取表约束
提问于 2013-01-09 14:12:59
您可以帮助我构造一个查询，检索所有表中的约束，每个表中的约束计数，并显示没有任何约束的表的NULL。
这就是我到目前为止所知道的：
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
Stack Overflow用户
发布于 2017-04-19 01:48:02
我使用了以下查询来检索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.nameStack Overflow用户
发布于 2017-08-26 03:49:51
您可以使用此查询获取
唯一约束，
带值的默认约束，
具有被引用的表和列的外键
和主键约束。
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');Stack Overflow用户
发布于 2014-07-02 18:56:24
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])
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14229277
复制相关文章
相似问题