Posted by: R Manimaran | July 14, 2013

SQL SERVER : List all the tables in a DB which contains Identity Columns

SQL SERVER : List all the tables in a DB which contains Identity Columns

In Sql Server we have some system tables and Views which will help us to identify the information about our custom Tables we have Created.

I have many tables in my DB and I want to list all the tables that contain the identity column.

The System Tables which I used here are

· SYS.TABLES

· SYS.IDENTITY_COLUMNS


SELECT SCHEMA_NAME(ST.[SCHEMA_ID])+'.'+OBJECT_NAME(ST.[OBJECT_ID])AS TableName,

SIC.name As [Column Name],

seed_value As [Seed Value],

increment_value As [INCREMENT VALUE],

ISNULL(last_value,0) AS [LAST IDENTITY VALUE]

FROM

SYS.TABLES ST JOIN SYS.IDENTITY_COLUMNS SIC

ON ST.object_id = SIC.object_id

The above will display the result as below

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: