SQL SERVER – 2005 – List All Column With Indentity Key In Specific Database

Question I received in Email : How to list all the columns in the database which are used as identity key in my database?

Answer: Run following query in query editor.
USE AdventureWorks
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,
t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – 2005 – List All Column With Indentity Key In Specific Database

  1. Another method that works in all versions is

    select table_schema, table_name,column_name from information_schema.columns
    where columnproperty(object_id(table_name),column_name,’IsIdentity’)=1
    order by table_schema, table_name

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

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