SQL 如何获取具有标识列的所有表的列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16044046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get list of all tables that has identity columns
提问by Allan Chua
I would like to learn how to fetch list of all tables that has identity columns from a MS SQL database.
我想学习如何从 MS SQL 数据库中获取具有标识列的所有表的列表。
回答by Aaron Bertrand
SELECT
[schema] = s.name,
[table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
);
回答by Sureshkumar
select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
回答by bopapa_1979
I like this approach because it uses a join instead of a WHERE EXISTS or a call to COLUMNPROPERTY. Note that the group by is only necessary if you a) have tables with more than one IDENTITY column and b) don't want duplicate results:
我喜欢这种方法，因为它使用连接而不是 WHERE EXISTS 或对 COLUMNPROPERTY 的调用。请注意，仅当您 a) 有包含多个 IDENTITY 列的表并且 b) 不想要重复结果时，才需要 group by：
SELECT
SchemaName = s.name,
TableName = t.name
FROM
sys.schemas AS s
INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.identity_columns AS ic on c.object_id = ic.object_id AND c.column_id = ic.column_id
GROUP BY
s.name,
t.name
ORDER BY
s.name,
t.name;
回答by user6232480
The script below will do:
下面的脚本将执行以下操作：
SELECT a.name as TableName,
CASE WHEN b.name IS NULL
THEN 'No Identity Column'
ELSE b.name
END as IdentityColumnName
FROM sys.tables a
LEFT JOIN sys.identity_columns b on a.object_id = b.object_id
回答by Muhammad Saleh
Select OBJECT_NAME(object_Id) Rrom sys.identity_columns where is_identity = 1;
选择 OBJECT_NAME(object_Id) Rrom sys.identity_columns where is_identity = 1;
