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?

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

• SQL SERVER – Jump in Identity Column After Restart
• SQL SERVER – DELETE, TRUNCATE and RESEED Identity
• SQL SERVER – Answer – Value of Identity Column after TRUNCATE command
• SQL SERVER – Finding Out Identity Column Without Using Column Name
• SQL SERVER – Enable Identity Insert – Import Expert Wizard
• SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
• SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table

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.

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