Day: November 12, 2024

How would you find all the tables in a SQL Server Database that have identity columns? Read on to find an easy query to identify them (see what I did there 🙂 )

Read more: Find SQL Server Tables with Identity Columns Easily

Here is a listing of some tables in my database. I need to find all of the tables that have an identity column set up.

The slow way is to open up each table in design view and look at each column.

After opening up the table you would then need to check each field to see if identify specification is set to YES.

This way is slow.

The easiest way to check your database is to run the query below. This will show every table and the column name that is set to Yes.

SELECT DB_Name() AS DatabaseName
,OBJECT_NAME(c.OBJECT_ID) AS TableName
,c.NAME AS ColumnName
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE is_identity = 1

Let us look at the Dim_City table in design view. After opening it up, we can see that CityId has Identity Specification set to Yes. This matches what the query turned up.

Do not be afraid of the database system views. They can be very helpful in documenting your database and are much quicker than clicking around in the GUI.