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
Advertisements
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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

  • Recent Posts

    • 415,065 hits
  • Join 37 other followers

  • Archives

  • Categories

  • September 2011
    M T W T F S S
    « Jul   Nov »
     1234
    567891011
    12131415161718
    19202122232425
    2627282930  
  • Advertisements
%d bloggers like this: