Identifying tables with IDENTITY columns in SQL Server

Hey guys,
Good morning!

In this quick post I will demonstrate how to identify tables that have IDENTITY columns in SQL Server. We often need to identify which tables these are, what the current value of the identity is and what the value of the increment (seed) of the table is, and of course we are not going to look at it table by table.

To do this, we can quickly identify this information with the query below:

SELECT
    IDENT_SEED(TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 
    AND TABLE_TYPE = 'BASE TABLE'
ORDER BY
    Current_Identity DESC

Example:

Identifying the columns that have the Identity property:

SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    ORDINAL_POSITION,
    NUMERIC_PRECISION,
    NUMERIC_SCALE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY
    TABLE_NAME 

Example:

Until next time!

sql, sql server, tables with identity, identity columns, How to get current identity number of specific table, how to retrieve the current identity value, last identity value