Show all tables having identity columns

Posted by Shyam | Posted in Database, SQL Server | Posted on 18-05-2009

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')

Comments (1)

KrisBelucci said on 02-06-2009
	Hi, good post. I have been wondering about this issue,so thanks for posting.

The Final Guide

Show all tables having identity columns

You may also like to read

Comments (1)

Post a comment

Categories

recent posts

recent comments

Subscribe

Partner Links

Archives

Statistics