If you’ve ever queried the sys.identity_columns view in SQL Server, you’ll know that it returns a list of identity columns. It also returns the object_id of the table, but not the table name or its schema.
You can use the code examples in this article to return the identity columns, along with their respective tables and schemas.
Example 1 – Return All Results
This example uses a join with sys.objects to return table and schema information
USE WideWorldImportersDW; SELECT SCHEMA_NAME(o.schema_id) AS [schema], OBJECT_NAME(ic.object_id) AS [object], o.type_desc, ic.name, ic.seed_value, ic.increment_value, ic.last_value FROM sys.identity_columns ic INNER JOIN sys.objects o ON o.object_id = ic.object_id ORDER BY SCHEMA_NAME(o.schema_id) ASC;
Result:
+-------------+---------------------------+----------------+------------------------------+--------------+-------------------+--------------+ | schema | object | type_desc | name | seed_value | increment_value | last_value | |-------------+---------------------------+----------------+------------------------------+--------------+-------------------+--------------| | Fact | Movement | USER_TABLE | Movement Key | 1 | 1 | 236667 | | Fact | Order | USER_TABLE | Order Key | 1 | 1 | 231412 | | Fact | Purchase | USER_TABLE | Purchase Key | 1 | 1 | 8367 | | Fact | Sale | USER_TABLE | Sale Key | 1 | 1 | 228265 | | Fact | Stock Holding | USER_TABLE | Stock Holding Key | 1 | 1 | 227 | | Fact | Transaction | USER_TABLE | Transaction Key | 1 | 1 | 99585 | | Integration | Customer_Staging | USER_TABLE | Customer Staging Key | 1 | 1 | NULL | | Integration | Employee_Staging | USER_TABLE | Employee Staging Key | 1 | 1 | NULL | | Integration | Movement_Staging | USER_TABLE | Movement Staging Key | 1 | 1 | NULL | | Integration | Order_Staging | USER_TABLE | Order Staging Key | 1 | 1 | NULL | | Integration | PaymentMethod_Staging | USER_TABLE | Payment Method Staging Key | 1 | 1 | NULL | | Integration | Purchase_Staging | USER_TABLE | Purchase Staging Key | 1 | 1 | NULL | | Integration | Sale_Staging | USER_TABLE | Sale Staging Key | 1 | 1 | NULL | | Integration | StockHolding_Staging | USER_TABLE | Stock Holding Staging Key | 1 | 1 | NULL | | Integration | StockItem_Staging | USER_TABLE | Stock Item Staging Key | 1 | 1 | NULL | | Integration | Supplier_Staging | USER_TABLE | Supplier Staging Key | 1 | 1 | NULL | | Integration | Transaction_Staging | USER_TABLE | Transaction Staging Key | 1 | 1 | NULL | | Integration | TransactionType_Staging | USER_TABLE | Transaction Type Staging Key | 1 | 1 | NULL | | Integration | City_Staging | USER_TABLE | City Staging Key | 1 | 1 | NULL | | sys | queue_messages_2041058307 | INTERNAL_TABLE | queuing_order | 0 | 1 | NULL | | sys | sqlagent_job_history | INTERNAL_TABLE | instance_id | 1 | 1 | NULL | | sys | sqlagent_jobsteps_logs | INTERNAL_TABLE | log_id | 1 | 1 | NULL | | sys | queue_messages_1977058079 | INTERNAL_TABLE | queuing_order | 0 | 1 | NULL | | sys | queue_messages_2009058193 | INTERNAL_TABLE | queuing_order | 0 | 1 | NULL | +-------------+---------------------------+----------------+------------------------------+--------------+-------------------+--------------+
Example 2 – Return Only User Tables
This example uses an inner join with sys.tables to return only identity columns from user tables.
USE WideWorldImportersDW; SELECT SCHEMA_NAME(t.schema_id) AS [schema], OBJECT_NAME(ic.object_id) AS [table], ic.name, ic.seed_value, ic.increment_value, ic.last_value FROM sys.identity_columns ic INNER JOIN sys.tables t ON t.object_id = ic.object_id ORDER BY SCHEMA_NAME(t.schema_id) ASC;
Result:
+-------------+-------------------------+------------------------------+--------------+-------------------+--------------+ | schema | table | name | seed_value | increment_value | last_value | |-------------+-------------------------+------------------------------+--------------+-------------------+--------------| | Fact | Movement | Movement Key | 1 | 1 | 236667 | | Fact | Order | Order Key | 1 | 1 | 231412 | | Fact | Purchase | Purchase Key | 1 | 1 | 8367 | | Fact | Sale | Sale Key | 1 | 1 | 228265 | | Fact | Stock Holding | Stock Holding Key | 1 | 1 | 227 | | Fact | Transaction | Transaction Key | 1 | 1 | 99585 | | Integration | City_Staging | City Staging Key | 1 | 1 | NULL | | Integration | Customer_Staging | Customer Staging Key | 1 | 1 | NULL | | Integration | Employee_Staging | Employee Staging Key | 1 | 1 | NULL | | Integration | Movement_Staging | Movement Staging Key | 1 | 1 | NULL | | Integration | Order_Staging | Order Staging Key | 1 | 1 | NULL | | Integration | PaymentMethod_Staging | Payment Method Staging Key | 1 | 1 | NULL | | Integration | Purchase_Staging | Purchase Staging Key | 1 | 1 | NULL | | Integration | Sale_Staging | Sale Staging Key | 1 | 1 | NULL | | Integration | StockHolding_Staging | Stock Holding Staging Key | 1 | 1 | NULL | | Integration | StockItem_Staging | Stock Item Staging Key | 1 | 1 | NULL | | Integration | Supplier_Staging | Supplier Staging Key | 1 | 1 | NULL | | Integration | Transaction_Staging | Transaction Staging Key | 1 | 1 | NULL | | Integration | TransactionType_Staging | Transaction Type Staging Key | 1 | 1 | NULL | +-------------+-------------------------+------------------------------+--------------+-------------------+--------------+