Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Friday, June 27, 2008 8:57 AM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: Finding IDENTITY columns

SELECT * FROM sys.syscolumns WHERE colstat = 1
7/11/2008 7:11 AM | Ramesh
Gravatar

# re: Finding IDENTITY columns

SQL IDENTITY COLUMN QUERY
8/7/2008 1:48 AM | Muralikrishna
Gravatar

# re: Finding IDENTITY columns

PLEASE SEND ME THE QUERY
7/7/2010 3:25 AM | deepthi
Gravatar

# re: Finding IDENTITY columns

Ramesh is wrong ColStat is for Primary key identification.
7/22/2010 12:58 PM | AlwynDuraisingh
Gravatar

# re: Finding IDENTITY columns

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.

snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:37 AM | furry boots
Gravatar

# re: Finding IDENTITY columns

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under simple video editing function embedded, support TRIM, CUT, CROP, and Join video files. you may use it as an video joiner for mac
10/26/2010 1:37 AM | hanly
Gravatar

# oakley sunglasses for sale

My friend and I were just having a debate about the topic you mentioned in your blog and apparently, I am the winner of the argument! Thanks so much.
4/22/2011 12:36 PM | job
Gravatar

# re: Finding IDENTITY columns

cheap mac cosmetics
wholesale mac eyeshadow
wholesale mac foundation
wholesale mac blush
6/1/2011 2:05 AM | buyulove
Gravatar

# re: Finding IDENTITY columns

This is a nice site ,good fresh interface and nice informative articles
8/2/2011 12:39 AM | registryspeeder
Gravatar

# 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
8/9/2011 2:58 PM | Sreedhar Vankayala
Gravatar

# re: Finding IDENTITY columns

Sreedhar Vankayala,

Thanks for your note about getting identity column name on different schema.
10/27/2011 7:58 AM | Oleg
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET