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

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

SQL SERVER - 2005 - List All Column With Identity Key In Specific Database identityvalue-800x584

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

Here are few additional blog posts which discuss about identity

Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table.

Answer: There are two answers – No and Yes. Let us see them one by one.

Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server. You will just have to add a new column.

Answer Yes – If you want to add new column to the table, it is totally possible to do so with the help of following a script.

1
ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)

Reference : Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System Table
Previous Post
SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info
Next Post
SQL SERVER – Introduction to Heap Structure – What is Heap?

Related Posts

2 Comments. Leave new

  • This will provide you with the columns that have Identity insert on, but not the Key columns

    Reply
  • 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

    Reply

Leave a Reply