How Many Columns In Table and Which Have Identity Columns

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) and make it a little easier to search for. I created it a few years ago and slightly modified it for what I needed. Executing this statement will return the tables with identity columns and tell you how many columns are in the table.

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

I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald