Justin Cooney

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:

• MSDN Documentation of sys.tables
• MSDN Documentation of sys.indexes
• DatabaseJournal.com Overview of sp_msforeachdb & Other Undocumented Stored Procedures