SQL Tips #35 - Finding Tables with Missing Identity Fields

This query finds tables that have no identity field defined.

The Query

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' 

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags