Retrieving all identity columns in a database in SQL Server

We can retrieve the identity for all tables in a database by using the below query:

SELECT  
  TABLE_NAME
, COLUMN_NAME
, IDENT_SEED(TABLE_NAME) AS "Identity Seed"
, IDENT_INCR(TABLE_NAME) AS "Identity Increment"
, COLUMN_DEFAULT
, DATA_TYPE
, IS_NULLABLE
, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
  AND TABLE_NAME != 'sysdiagrams'
ORDER by TABLE_NAME
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: