[sql-server] How do you determine what SQL Tables have an identity column programmatically

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL.

Results would be something like:

TableName, ColumnName

This question is related to sql-server tsql metadata identity-column

The answer is


I think this works for 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

This worked for SQL Server 2005, 2008, and 2012. I found that the sys.identity_columns did not contain all my tables with 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;

Looking at the documentation page the status column can also be utilized. Also you can add the four part identifier and it will work across different servers.

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


By some reason sql server save some identity columns in different tables, the code that work for me, is the following:

select      TABLE_NAME tabla,COLUMN_NAME columna
from        INFORMATION_SCHEMA.COLUMNS
where       COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
union all
select      o.name tabla, c.name columna
from        sys.objects o 
inner join  sys.columns c on o.object_id = c.object_id
where       c.is_identity = 1

sys.columns.is_identity = 1

e.g.,

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

here's a working version for MSSQL 2000. I've modified the 2005 code found here: 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;

sys.columns.is_identity = 1

e.g.,

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

Another way (for 2000 / 2005/2012/2014):

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

NOTE: table_name_here should be schema.table, unless the schema is dbo.


I think this works for 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

I think this works for 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

Use this :

DECLARE @Table_Name VARCHAR(100) 
DECLARE @Column_Name VARCHAR(100)
SET @Table_Name = ''
SET @Column_Name = ''

SELECT  RowNumber = ROW_NUMBER() OVER ( PARTITION BY T.[Name] ORDER BY T.[Name], C.column_id ) ,
    SCHEMA_NAME(T.schema_id) AS SchemaName ,
    T.[Name] AS Table_Name ,
    C.[Name] AS Field_Name ,
    sysType.name ,
    C.max_length ,
    C.is_nullable ,
    C.is_identity ,
    C.scale ,
    C.precision
FROM    Sys.Tables AS T
    LEFT JOIN Sys.Columns AS C ON ( T.[Object_Id] = C.[Object_Id] )
    LEFT JOIN sys.types AS sysType ON ( C.user_type_id = sysType.user_type_id )
WHERE   ( Type = 'U' )
    AND ( C.Name LIKE '%' + @Column_Name + '%' )
    AND ( T.Name LIKE '%' + @Table_Name + '%' )
ORDER BY T.[Name] ,
    C.column_id

In SQL 2005:

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

Another way (for 2000 / 2005/2012/2014):

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

NOTE: table_name_here should be schema.table, unless the schema is dbo.


List of tables without Identity column based on Guillermo answer:

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

In SQL 2005:

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

This query seems to do the trick:

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')

The following query work for me:

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

This worked for me using Sql Server 2008:

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

sys.columns.is_identity = 1

e.g.,

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

Another way (for 2000 / 2005/2012/2014):

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

NOTE: table_name_here should be schema.table, unless the schema is dbo.


This worked for SQL Server 2005, 2008, and 2012. I found that the sys.identity_columns did not contain all my tables with 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;

Looking at the documentation page the status column can also be utilized. Also you can add the four part identifier and it will work across different servers.

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


In SQL 2005:

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

here's a working version for MSSQL 2000. I've modified the 2005 code found here: 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;

I think this works for 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

Use this :

DECLARE @Table_Name VARCHAR(100) 
DECLARE @Column_Name VARCHAR(100)
SET @Table_Name = ''
SET @Column_Name = ''

SELECT  RowNumber = ROW_NUMBER() OVER ( PARTITION BY T.[Name] ORDER BY T.[Name], C.column_id ) ,
    SCHEMA_NAME(T.schema_id) AS SchemaName ,
    T.[Name] AS Table_Name ,
    C.[Name] AS Field_Name ,
    sysType.name ,
    C.max_length ,
    C.is_nullable ,
    C.is_identity ,
    C.scale ,
    C.precision
FROM    Sys.Tables AS T
    LEFT JOIN Sys.Columns AS C ON ( T.[Object_Id] = C.[Object_Id] )
    LEFT JOIN sys.types AS sysType ON ( C.user_type_id = sysType.user_type_id )
WHERE   ( Type = 'U' )
    AND ( C.Name LIKE '%' + @Column_Name + '%' )
    AND ( T.Name LIKE '%' + @Table_Name + '%' )
ORDER BY T.[Name] ,
    C.column_id

List of tables without Identity column based on Guillermo answer:

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

sys.columns.is_identity = 1

e.g.,

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

The following query work for me:

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

This query seems to do the trick:

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')

Another way (for 2000 / 2005/2012/2014):

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

NOTE: table_name_here should be schema.table, unless the schema is dbo.


This worked for me using Sql Server 2008:

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

By some reason sql server save some identity columns in different tables, the code that work for me, is the following:

select      TABLE_NAME tabla,COLUMN_NAME columna
from        INFORMATION_SCHEMA.COLUMNS
where       COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
union all
select      o.name tabla, c.name columna
from        sys.objects o 
inner join  sys.columns c on o.object_id = c.object_id
where       c.is_identity = 1

In SQL 2005:

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

This query seems to do the trick:

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')

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to metadata

VS 2017 Metadata file '.dll could not be found Clean out Eclipse workspace metadata Retrieving and Saving media metadata using FFmpeg How to query the permissions on an Oracle directory? How to find available directory objects on Oracle 11g system? How do I find the date a video (.AVI .MP4) was actually recorded? Sql Query to list all views in an SQL Server 2005 database How do I list all tables in all databases in SQL Server in a single result set? List of foreign keys and the tables they reference in Oracle DB From a Sybase Database, how I can get table description ( field names and types)?

Examples related to identity-column

BULK INSERT with identity (auto-increment) column Inserting into Oracle and retrieving the generated sequence ID Adding an identity to an existing column How do you determine what SQL Tables have an identity column programmatically