如何列出SQL Server中具有数据库标识插入的所有表的约束？ [英] How to list the constraints for all tables with identity insert of a database in SQL server?

问题描述

How to  find constraints for tables with identity insert for a database in Sql server?

我能够获取表格标识插入机智

I am able to get the Tables with Identity Insert wit

How to  list the  constraints for all tables with identity insert of a database in SQL server?h below query
<pre lang="SQL">       select TABLE_NAME
       from INFORMATION_SCHEMA.COLUMNS
       where TABLE_SCHEMA = 'dbo'
      and 
COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
      ORDER BY ORDINAL_POSITION</pre>

I need to fetch the constraints of the tables  with identity insert column alone.
I am able to fetch all constraints for a DB with below query. but it's needed for tables with identity insert alone
<pre lang="SQL">SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE &#39;%CONSTRAINT&#39;</pre>

我有什么试过：



选择TABLE_NAME
来自INFORMATION_SCHEMA.COLUMNS的


其中TABLE_SCHEMA ='dbo'

和

COLUMNPROPERTY（object_id（TABLE_NAME），COLUMN_NAME，'IsIdentity'）= 1

ORDER BY ORDINAL_POSITION



我可以使用以下查询获取数据库的所有约束。但是单独使用标识插件的表需要它

What I have tried:

select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and
COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY ORDINAL_POSITION

I am able to fetch all constraints for a DB with below query. but it's needed for tables with identity insert alone

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

推荐答案

您可以执行另一个（自我）连接到sys.objects表来限制表格，例如

You can do another (self) join to the sys.objects table to restrict the tables e.g.

SELECT TABS.name, CONS.name, CONS.type_desc
FROM sys.objects TABS
JOIN sys.columns COLS ON TABS.object_id = COLS.object_id
JOIN sys.objects CONS ON TABS.object_id = CONS.parent_object_id AND CONS.type_desc LIKE '%CONSTRAINT'
WHERE TABS.type_desc='USER_TABLE'
AND SCHEMA_NAME(TABS.schema_id) = 'dbo'
AND COLS.is_identity = 1

这篇关于如何列出SQL Server中具有数据库标识插入的所有表的约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！