Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)

 

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

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

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.