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

Identity Key columns generate values automatically when rows are inserted. When you inherit a database, a quick inventory can help you see where those columns are used and what seed and increment each one has.

Find identity columns in the current database

Run this query in the database you want to inspect. It returns the schema, table and column names along with the identity seed and increment.

SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
       t.name AS table_name,
       c.name AS column_name,
       c.seed_value,
       c.increment_value
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS c
    ON c.object_id = t.object_id
ORDER BY schema_name, table_name, column_name;

The query uses SQL Server catalog views, so it lists tables in the current database rather than every database on the instance. Metadata visibility depends on your permissions. If a table is missing, check that you are in the right database and have permission to see its metadata.

Can an existing column become an identity column?

You cannot add the IDENTITY property to an existing column with ALTER COLUMN. You can, however, add a new identity column to a table:

CREATE TABLE #IdentityDemo (ItemName varchar(50));
INSERT INTO #IdentityDemo (ItemName)
VALUES ('First'), ('Second');

ALTER TABLE #IdentityDemo
ADD ItemId int IDENTITY(1,1);

SELECT ItemId, ItemName FROM #IdentityDemo;
DROP TABLE #IdentityDemo;

Here the seed and increment are both 1. Existing rows receive generated values when the column is added, but do not assume they will be assigned in any particular order. Test the change on a copy of your data first and review its impact on dependent queries and applications.

Identity does not replace a key constraint

An identity property generates values, but it does not itself enforce uniqueness. Add a primary key or unique constraint when your design requires one. Also, identity values can have gaps, so do not use them as a promise of consecutive numbering.

More about identity columns

• Identity values after a restart
• DELETE, TRUNCATE and reseeding identity
• Identity values after TRUNCATE
• Finding an identity column without its name
• Enable identity insert
• Compare identity value functions
• Add an identity column after creating a table

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.