Find all Tables with and without Identity column in SQL Server Database

To find all user tables with an Identity column in SQL Server database, use this query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, t.name AS Table_Name
, ic.name AS Identity_Column_Name
FROM sys.tables t
INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
INNER JOIN sys.identity_columns ic on t.object_id = ic.object_id
WHERE OBJECTPROPERTY(t.object_id,'TableHasIdentity') = 1
AND t.type = 'U'
ORDER BY t.name

It shows the table name and the name of the identity column. Only one identity column can be created per table.

To find all user tables without an Identity column in SQL Server database, use this query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, t.name AS Table_Name
FROM sys.tables t
INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE OBJECTPROPERTY(t.object_id,'TableHasIdentity') = 0
AND t.type = 'U'
ORDER BY t.name

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">