SQL-Server:- Get all table that doesn’t have the identity column

SQL-Server:- Get all table that doesn’t have the identity column

access_timeMonday, 7 May, 2018 chat_bubble_outline0 comments
Identity column should be present in every table, that's one of the best practices every database developer used or recommended.
 
The identity column is an auto-increment value, most of the cases we have created identity column of a table as a primary key. that is very helpful whenever we want distinct value for every row, for writing some queries.
 
If any condition we have forgotten to create an identity column in the tables, or we need to identify the tables which don't have the identity column in a DB. the developer doesn't go & check every table one by one.   
 
We can write a single query to fetch all the tables in a Database which don't have the identity column
Sql-Server Identity Column.
 
SELECT table_name 
FROM   information_schema.tables 
WHERE  table_name NOT IN (SELECT DISTINCT c.table_name 
                          FROM   information_schema.columns c 
                                 INNER JOIN sys.identity_columns ic 
                                         ON ( c.column_name = ic.NAME )) 
       AND table_type = 'BASE TABLE';
 
You can also apply some more filters like Database name etc on the above query for finding the more accurate result.
 
Please share your suggestions and thoughts about this article in the comment box.
folder_openAssigned tags

0 Comments

Leave a Comment: