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;

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

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


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: