I recently had to find a way to look up how many columns were in a table and which of those tables had identity columns. After digging through some of my old scripts, I found this one. It did the trick and I thought that I would make it a little easier on myself (and others searching for something like this)
, (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TABLES.TABLE_NAME ) AS NumCols
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'
Running this statement against AdventureWorks2008R2 you may have similar results to those shown in figure 1 below. By removing the TableHasIdentity you will have 71 tables returned rather than just 39 (assuming you haven’t changed your database of course).
Figure 1: Results
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting