Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

Finding IDENTITY columns

As database administrators and developers, it's our job to know the in's and out's of the data in our charge. Whether we're designing entity relationships to support underlying business requirements, optimizing indexes to achieve the best response times, or writing a query to retrieve the right data in the most efficient manner, having an intimate knowledge of the database is key.

However, since few of us are responsible for just one database, it's sometimes difficult to gain, much less maintain, that level of familiarity with our data.

Fortunately for us, however, we have the metadata available to us. We can use SQL Server's system tables and views to seek out the information we need to help us do our jobs.

For example, I was recently asked by a client to begin considering what it would take to retrofit replication into an existing application. Now obviously it's best to know before the design phase ever begins that replication may be an option, but atlas hindsight is always 20-20 and circumstances change.

One of my first thoughts was that of identity columns. If identity columns are widespread throughout a database design, that must be addressed as replication is considered.

So, how many tables in my client's employ identity columns? Good question. I don't know off-hand. But fortunately the information is at my fingertips.

The following query can be used to list all tables in a database where an identity column is defined.

SELECT         
t.TABLE_NAME
 ,c.COLUMN_NAME
 ,c.TABLE_CATALOG
 ,c.TABLE_SCHEMA
FROM

INFORMATION_SCHEMA.COLUMNS AS c JOIN
 INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE
COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
,c.COLUMN_NAME,'IsIdentity') = 1 AND
 t.TABLE_TYPE = 'Base Table' AND
 t.TABLE_NAME NOT LIKE 'dt%' AND
 t.TABLE_NAME NOT LIKE 'MS%' AND
 t.TABLE_NAME NOT LIKE 'syncobj_%'


Running the query in the AdventureWorksDW database produces the following results.

Identity_columns-2008-06-27

Cheers!

Joe


kick it on DotNetKicks.com

Legacy Comments


Ramesh
2008-07-11
re: Finding IDENTITY columns
SELECT * FROM sys.syscolumns WHERE colstat = 1

Muralikrishna
2008-08-07
re: Finding IDENTITY columns
SQL IDENTITY COLUMN QUERY

deepthi
2010-07-07
re: Finding IDENTITY columns
PLEASE SEND ME THE QUERY

AlwynDuraisingh
2010-07-22
re: Finding IDENTITY columns
Ramesh is wrong ColStat is for Primary key identification.

Sreedhar Vankayala
2011-08-09
re: Finding IDENTITY columns
The query may not work for tables in different schema ... Change the query to ... COLUMNPROPERTY(object_id('[' + Table_Schema + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') = 1

Regards

Sreedhar Vankayala

Oleg
2011-10-27
re: Finding IDENTITY columns
Sreedhar Vankayala,

Thanks for your note about getting identity column name on different schema.