List All Identity Columns in a Database in SQL Server

Problem: Received a request for a list of all identity columns existing in a database.  The example is from the SQL 2016 sample database: WideWorldImporters

SELECT
 o.name as 'OWNER',
 t.name AS 'TABLENAME',
 i.name AS 'COLUMN'
FROM sys.schemas AS o
INNER JOIN sys.tables AS t
 ON o.[schema_id] = t.[schema_id]
INNER JOIN sys.identity_columns i
ON i.[object_id] = t.[object_id]
order by 1,2,3;

All Identity Columns In A Database Results

Leave a Reply