如何通过编程确定哪些SQL表具有标识列

Question

我想在SQL Server 2005中创建一个包含标识列以及它们对应的表格的列列表。

结果应该类似于:

TableName, ColumnName

- Gabe

Answer 1

对于 SQL Server，另一个潜在的方法是使用 INFORMATION_SCHEMA 视图来执行此操作，这种方法对系统表的依赖性较小（这些表因版本而异，可能会发生变化）：

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME 

- DaveCrawford

---

请注意一个问题，您可以指定[数据库名称] .information_schema.columns，但是从不同的数据库运行...然后COLUMNPROPERTY正在针对错误的数据库运行。 - Mike M

如果你有其他的模式，这种方式更好：使用COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME)... - Hossein Margani

我认为这个答案在 Microsoft SQL Server 2014 上不起作用。 - ChrisW

如果您正在寻找一种从SQL Server 2000起就可行的简单方法，请查看@Guillermo的这个答案。 - user692942

INFORMATION_SCHEMA.COLUMNS 包含普通表和 视图 的信息，我建议添加 TABLE_TYPE（连接 INFORMATION_SCHEMA.TABLES）以提高结果集的可读性。 - Diego Scaravaggi

针对SQL 2017+的修改，同时锁定到特定数据库SELECT [COLUMN_NAME] , [TABLE_NAME] , [TABLE_CATALOG] FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE COLUMNPROPERTY(OBJECT_ID(CONCAT_WS('.' ,[TABLE_CATALOG] ,[TABLE_SCHEMA] ,[TABLE_NAME])) ,[COLUMN_NAME] ,'IsIdentity') = 1 ORDER BY [TABLE_NAME] - Rax

Answer 2

sys.columns.is_identity = 1

例如：

select o.name, c.name
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1

- kcrumley

---

注意：在SQL 2008中，这对我有效，而被接受的答案则不行（问题要求是SQL 2005）。 - Daniel Schaffer

这个答案同样适用于 Microsoft SQL Server 2014。 - ChrisW

Answer 3

另一种方法（适用于2000/2005/2012/2014）：

IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'table_name_here'), 'TableHasIdentity')) = 1)
    PRINT 'Yes'
ELSE
    PRINT 'No'

注意：`table_name_here` 应为 `schema.table`，除非 schema 是 `dbo`。

- Guillermo

Answer 4

在SQL 2005中：

select object_name(object_id), name
from sys.columns
where is_identity = 1

- Euro Micelli

Answer 5

基于Guillermo的回答，以下是不含自增列的表格列表:

SELECT DISTINCT TABLE_NAME
FROM            INFORMATION_SCHEMA.COLUMNS
WHERE        (TABLE_SCHEMA = 'dbo') AND (OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 0)
ORDER BY TABLE_NAME

- Sergey

Answer 6

这个查询似乎可以解决问题：

SELECT 
    sys.objects.name AS table_name, 
    sys.columns.name AS column_name
FROM sys.columns JOIN sys.objects 
    ON sys.columns.object_id=sys.objects.object_id
WHERE 
    sys.columns.is_identity=1
    AND
    sys.objects.type in (N'U')

- Manrico Corazzi

Answer 7

这是适用于MSSQL 2000的可行版本。我修改了在此处找到的2005代码：http://sqlfool.com/2011/01/identity-columns-are-you-nearing-the-limits/

/* Define how close we are to the value limit
   before we start throwing up the red flag.
   The higher the value, the closer to the limit. */
DECLARE @threshold DECIMAL(3,2);
SET @threshold = .85;

/* Create a temp table */
CREATE TABLE #identityStatus
(
      database_name     VARCHAR(128)
    , table_name        VARCHAR(128)
    , column_name       VARCHAR(128)
    , data_type         VARCHAR(128)
    , last_value        BIGINT
    , max_value         BIGINT
);

DECLARE @dbname sysname;
DECLARE @sql nvarchar(4000);

-- Use an cursor to iterate through the databases since in 2000 there's no sp_MSForEachDB command...

DECLARE c cursor FAST_FORWARD FOR
SELECT
    name
FROM
    master.dbo.sysdatabases 
WHERE 
    name NOT IN('master', 'model', 'msdb', 'tempdb');

OPEN c;

FETCH NEXT FROM c INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'Use [' + @dbname + '];
    Insert Into #identityStatus
    Select ''' + @dbname + ''' As [database_name]
        , Object_Name(id.id) As [table_name]
        , id.name As [column_name]
        , t.name As [data_type]
        , IDENT_CURRENT(Object_Name(id.id)) As [last_value]
        , Case 
            When t.name = ''tinyint''   Then 255 
            When t.name = ''smallint''  Then 32767 
            When t.name = ''int''       Then 2147483647 
            When t.name = ''bigint''    Then 9223372036854775807
          End As [max_value]
    From 
        syscolumns As id
        Join systypes As t On id.xtype = t.xtype
    Where 
        id.colstat&1 = 1    -- this identifies the identity columns (as far as I know)
    ';

    EXECUTE sp_executesql @sql;

    FETCH NEXT FROM c INTO @dbname;
END

CLOSE c;
DEALLOCATE c;

/* Retrieve our results and format it all prettily */
SELECT database_name
    , table_name
    , column_name
    , data_type
    , last_value
    , CASE 
        WHEN last_value < 0 THEN 100
        ELSE (1 - CAST(last_value AS FLOAT(4)) / max_value) * 100 
      END AS [percentLeft]
    , CASE 
        WHEN CAST(last_value AS FLOAT(4)) / max_value >= @threshold
            THEN 'warning: approaching max limit'
        ELSE 'okay'
        END AS [id_status]
FROM #identityStatus
ORDER BY percentLeft;

/* Clean up after ourselves */
DROP TABLE #identityStatus;

- S.E.

Answer 8

以下查询对我有效：

select  TABLE_NAME tabla,COLUMN_NAME columna
from    INFORMATION_SCHEMA.COLUMNS
where   COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

- Jorge Santos Neill

Answer 9

这适用于SQL Server 2005、2008和2012。我发现sys.identity_columns并没有包含所有包含标识列的表。

SELECT a.name AS TableName, b.name AS IdentityColumn
FROM sys.sysobjects a 
JOIN sys.syscolumns b 
ON a.id = b.id
WHERE is_identity = 1
ORDER BY name;

查看文档页面，状态列也可以被利用。另外，您可以添加四个部分的标识符，使其在不同的服务器上工作。

SELECT a.name AS TableName, b.name AS IdentityColumn
FROM [YOUR_SERVER_NAME].[YOUR_DB_NAME].sys.sysobjects a 
JOIN [YOUR_SERVER_NAME].[YOUR_DB_NAME].sys.syscolumns b 
ON a.id = b.id
WHERE is_identity = 1
ORDER BY name;

Source: https://msdn.microsoft.com/en-us/library/ms186816.aspx

- Nikolai Bielik

Answer 10

我认为这适用于SQL 2000：

SELECT 
    CASE WHEN C.autoval IS NOT NULL THEN
        'Identity'
    ELSE
        'Not Identity'
    AND
FROM
    sysobjects O
INNER JOIN
    syscolumns C
ON
    O.id = C.id
WHERE
    O.NAME = @TableName
AND
    C.NAME = @ColumnName

- brian

---

我不知道autoval是什么，但对于我所有的标识字段，它都为NULL。我拥有的可用的SQL 2000代码是：where colstat & 1 = 1我不确定这段代码来自哪里（已经有5年了），但我的注释说需要一个位掩码。但是对于我的标识，colstat = 1。 - Kevin

嗯...我使用状态和128 = 128来确定我的身份 :-P - Brimstedt