You can use T-SQL to return a list of identity columns in a database in SQL Server.
You can do this using the sys.identity_columns system catalog view.
Example 1 – Basic Usage
Here’s an example to demonstrate.
USE Test; SELECT OBJECT_NAME(object_id) AS [object], name, seed_value, increment_value, last_value, is_not_for_replication FROM sys.identity_columns;
Result:
+---------------------------+------------------+--------------+-------------------+--------------+--------------------------+ | object | name | seed_value | increment_value | last_value | is_not_for_replication | |---------------------------+------------------+--------------+-------------------+--------------+--------------------------| | ConstraintTest | ConstraintTestId | 1 | 1 | 17 | 0 | | sqlagent_job_history | instance_id | 1 | 1 | NULL | 0 | | sqlagent_jobsteps_logs | log_id | 1 | 1 | NULL | 0 | | Cats | id | 1 | 1 | 2 | 0 | | Dogs | id | 1 | 1 | 1 | 0 | | t1 | id | 1 | 1 | 2 | 0 | | t2 | id | 150 | 10 | 160 | 0 | | scope_identity_test | id | 1 | 1 | 3 | 0 | | Event | EventId | 1 | 1 | NULL | 0 | | Scoreboard | ScoreId | 1 | 1 | 8 | 0 | | Pets | PetId | 1 | 1 | 5 | 0 | | BestFriends | pet_id | 101 | 10 | 121 | 0 | | Cities | CityId | 150 | 10 | 180 | 0 | | Colors | ColorId | 1 | 1 | 6 | 0 | | queue_messages_1977058079 | queuing_order | 0 | 1 | NULL | 0 | | t6 | id | 1 | 1 | 1 | 0 | | t7 | id | 100 | 1 | 100 | 0 | | queue_messages_2009058193 | queuing_order | 0 | 1 | NULL | 0 | | queue_messages_2041058307 | queuing_order | 0 | 1 | NULL | 0 | +---------------------------+------------------+--------------+-------------------+--------------+--------------------------+
I used my Test database for this example. I chose this one because I’ve previously created identity columns with various seed increment values.
In this example I used the OBJECT_NAME() function to get the object’s name from the object_id column. In this case it’s the table name. So in other words, the first column lists the table name, and the second column lists the identity column.
Example 2 – List All Columns
In the previous example I only returned a handful of columns. This is because sys.identity_columns returns a lot of columns.
In this example I return only one identity column (i.e. one row), but I return all columns for that row. I use vertical output when displaying the results so that you aren’t forced to scroll sideways.
SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(object_id) = 'Cities';
Result (using vertical output):
object_id | 1970106059 name | CityId column_id | 1 system_type_id | 56 user_type_id | 56 max_length | 4 precision | 10 scale | 0 collation_name | NULL is_nullable | 0 is_ansi_padded | 0 is_rowguidcol | 0 is_identity | 1 is_filestream | 0 is_replicated | 0 is_non_sql_subscribed | 0 is_merge_published | 0 is_dts_replicated | 0 is_xml_document | 0 xml_collection_id | 0 default_object_id | 0 rule_object_id | 0 seed_value | 150 increment_value | 10 last_value | 180 is_not_for_replication | 0 is_computed | 0 is_sparse | 0 is_column_set | 0 generated_always_type | 0 generated_always_type_desc | NOT_APPLICABLE encryption_type | NULL encryption_type_desc | NULL encryption_algorithm_name | NULL column_encryption_key_id | NULL column_encryption_key_database_name | NULL is_hidden | 0 is_masked | 0 graph_type | NULL graph_type_desc | NULL
Most of these columns are inherited from sys.columns, but some are unique to sys.identity columns. For a detailed description of these columns, see the Microsoft documentation for sys.columns and sys.identity columns.
Including Tables & Schemas
You may have noticed that these examples don’t include the schema in the result set. See Including Tables & Schemas when Listing the Identity Columns if you need to return the schema. That article also includes an example of removing internal tables from the results.