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)

USE AdventureWorks2008R2

SELECT

      TABLE_NAME

      , (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TABLES.TABLE_NAME ) AS NumCols

FROM

      INFORMATION_SCHEMA.TABLES

WHERE

      OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableHasIdentity') = 1

      AND TABLE_TYPE = 'BASE TABLE'

ORDER BY

      TABLE_NAME

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

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter