How to identify whether the table has an identity column

sql

sql-server

identity-columns

system-catalog-views

byNikita Barsukov·Jan 28, 2025

To immediately determine if a specific table has an Identity column, take advantage of the sys.columns and sys.tables in this clean T-SQL query:

SELECT 
    t.name AS TableName, 
    c.name AS IdentityColumn
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    c.is_identity = 1 
    AND t.name = 'YourTableName'; -- replace with your table

The query will return the IdentityColumn in 'YourTableName'. If no rows return, the table has no identity column.

Detailed examination of identity columns

Understanding why identity columns matter is key in all operations involving SQL Server. Identity columns automatically generate unique values for each new record, promoting integrity and facilitating referencing.

Inspecting with system catalog views and functions

To broaden your options, capture the identity column using system catalog views and functions:

-- Is there an identity column? Let's find out!
IF OBJECTPROPERTY(OBJECT_ID('YourTableName'), 'TableHasIdentity') = 1
    PRINT 'Yes, we have an identity column.';
ELSE
    PRINT 'Nope, identity column is on vacation.';

Column-targeted checks

When you need to ensure a specific column has an identity, perform a column-targeted check:

SELECT 
    COLUMNPROPERTY(OBJECT_ID('YourTableName'),'ColumnName','IsIdentity') AS IsIdentity
WHERE
    OBJECT_NAME(OBJECT_ID) = 'YourTableName';

Salute to sys.identity_columns

Need a comprehensive view of identity columns? Look no further:

SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS IdentityColumn,
    seed_value, increment_value, last_value, is_not_for_replication
FROM 
    sys.identity_columns
WHERE 
    OBJECT_NAME(object_id) = 'YourTableName';

Watch your steps!

Remember, table names can change and always verify permissions. Querying system views may not be everyone's cup of tea.

Pro tips and tricks

Adopt these pro tips to streamline your identity-checking process:

Don't let the table creation sneak past you

-- Double check while creating the table
IF @@IDENTITY IS NOT NULL PRINT 'We nailed it. There is an identity column.'

Wrestling with replication or linked servers?

-- Cut to the chase when dealing with replication or linked servers
SELECT columnproperty(object_id('linkedServerName.dbName.schemaName.tableName'),'columnName','IsIdentity')

Tag along during database migrations

-- Keep an eye on identity columns before migrating data
IF (SELECT OBJECTPROPERTY(OBJECT_ID('YourTableName'), 'TableHasIdentity')) = 1
    PRINT 'Hold up! Watch for identity seed reset on migration!'