A simple script – find heap tables

I found I often need to find out those heap tables when I do quality control of the database schema in some projects. Usually those heap tables will be requested for justification. So here is the simple script used to make this task easier.

select ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ from sys.indexes i
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
where index_id = 0

And this script can also be changed to find out those heap tables with non-clustered indexes.

select ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ from sys.indexes i
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
group by s.name, t.name
having sum(index_id) = 0

Advertisements
Post a comment or leave a trackback: Trackback URL.

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

%d bloggers like this: