Programming, SQL, SQL Server 2008, SQL Tips, System Objects

SQL Server: Query All Clustered & NonClustered Indexes

A great way to review all of the Clustered and NonClustered Indexes set up for a particular database is to query the SQL Server system tables.

The sys.tables table contains the full list of tables for your database, and the sys.indexes table contains the full list of indexes. Simply join the two on their object_id columns and you have all of the information you could ever want about your indexes at your fingertips.

Without further ado, here is the query:

USE  MyDatabase
GO
SELECT b.name,b.type_desc,a.name,a.type_desc,a.*
FROM sys.indexes a
INNER JOIN sys.tables b ON a.object_id=b.object_id
WHERE a.type IN (1,2)

This is great if you want to review the indexes for a particular database on your instance of SQL Server. However if you want to review all indexes in all of the databases on your instance of SQL Server, then you can do so by adding the above query to the system sp_msforeachdb stored procedure.

The great thing about sp_msforeachdb is that it will loop through each database on your instance of SQL Server for you and execute your database-agnostic command on each.

So in practice, you can change the database-specific query above to the following in order to review all indexes in all of the databases on your instance of SQL Server:

sp_msforeachdb 'select "?" AS db, b.name,b.type_desc,a.name,a.type_desc,a.* 
FROM [?].sys.indexes a
INNER JOIN [?].sys.tables b ON a.object_id=b.object_id
WHERE a.type IN (1,2)'

Resources:

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