CODEWITHSUNDEEP
sqlsql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How to find tables that doesn't have an Auto Increment Primary Key in SQL Server

I have a database that contains hundreds of tables. As the passage of time multiple developers have worked on this and they also have created new tables several times. Now some of them always kept the Primary Key as auto increment/IDENTITY while some of them haven't. I want to get the names of tables which doesn't have Primary Key as IDENTITY and which does have.

Is there any query that can help me get through this instead of manually checking all tables ?

Upvotes: 0

Views: 793

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

try this script 

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

Upvotes: 2

Related Questions