The Final Guide

The following query will be very much helpful when your project manager asks you to write a query to show all the tables in a database (SQL Server 2000/2005/2008) which are having identity columns. Its very simple and straight forward.

Query for SQL Server 2005 / 2008

USE Northwind
GO

SELECT object_name(o.object_id) AS TableName, c.name as IdentityColumn
FROM sys.columns c JOIN sys.objects  o
ON c.object_id = o.object_id
WHERE c.is_identity = 1
AND o.type IN ('U')

Results

Query for SQL Server 2000

USE Northwind
GO

SELECT object_name(o.id) AS TableName, c.name as IdentityColumn
FROM syscolumns c JOIN sysobjects  o
ON c.id = o.id
WHERE c.colstat = 1
AND o.type IN ('U')

This entry was posted on Monday, May 18th, 2009 at 12:18 pm and is filed under Database, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.