Tables with Identity columns

How to Find Tables Using Identity Columns in SQL Server

If you're working with SQL Server and need to identify which tables use identity columns, you can accomplish this using T-SQL. An identity column is a column that automatically generates numeric values when new rows are inserted into the table.

Here's a T-SQL query to help you find the tables that contain identity columns:

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
    SELECT * FROM [sys].identity_columns i
    WHERE i.[object_id] = t.[object_id]
);
-- You can easily find tables without identity columns by using NOT EXISTS in the WHERE clause.

Listing All Identity Columns

To list all the identity columns in your database, you can use the following query:

SELECT COLUMN_NAME, TABLE_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1;
-- You can add 'ORDER BY TABLE_NAME' to sort the results.

This query will return all the identity columns along with their corresponding table names.

Viewing Details About Identity Columns

If you'd like to get more detailed information about the identity columns, such as the seed value, increment value, last generated value, and replication settings, you can use this query:

SELECT OBJECT_NAME(object_id) AS [object], name,
  seed_value,
  increment_value,
  last_value,
  is_not_for_replication
FROM sys.identity_columns;

This query provides essential information like how the identity column is incremented, the last value generated, and whether the identity column is configured for replication.

For further details on identity columns, check out this link.