SQL Tips by Namwar Rizvi

August 18, 2007

Finding tables which have Identity column

Filed under: Information,New Features,Object Search,SQL Server 2005,tips — namwar @ 11:04 PM

Following is a quick trick to find all the tables which have identity column.

USE Adventureworks;
GO

SELECT SCHEMA_NAME(schema_id) AS [Schema],name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,‘TableHasIdentity’) = 1
ORDER BY [Schema], [Table];
GO

Please note that if you want to find all those tables which do not have identity column then just change the where clause as follows:

WHERE OBJECTPROPERTY(object_id,‘TableHasIdentity’) = 0

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: