By: Greg Robidoux | Updated: 2020-02-24 | Comments (5) | Related: > Indexing
Problem
Sometimes there is a need to review all your indexes across your entire database. The need might be for simple analysis, optimization or maybe just for documentation purposes.
There are several built in utilities in SQL Server that allow you to take a look at your indexes, but none offer a simple approach to list all indexes for all tables. The options are as follows:
- Using SQL Server Management Studio you can navigate to the table and look at each index one at a time.
- Using the sp_helpindex stored procedure
- sp_helpindex products
- The problem with this approach is that you can only see one table at a time
- Querying the sysindexes table
- SELECT * FROM sysindexes
- The problem with this approach is that you need to write a complex query to make sense out of some of the data
- Using sp_msforeachtable
- sp_msforeachtable "sp_helpindex '?'"
- The problem with this approach is that each group is segmented into its own result set and you can't tell which table the results are for
So as you can see, there is not a simple approach for getting a list of all your indexes for all of your tables in one database.
Solution
To simplify gathering index information for all tables across the entire database a simple tweak to the sp_helpindex stored procedure allows for the retrieval of index information for all of the tables. So instead of having to do this one table at a time, you can now do this one database at a time.
Download the modified stored procedure sp_helpindex2.
NOTE: A subscriber pointed out that sp_helpindex2 did not handle objects owned by other users. This has been fixed in this version and also includes the user name in the output. Download sp_helpindex3
To use this stored procedure do the following:
- download one of the SPs above
- open this file in a Query window and change to the database you want to analyze
- then execute the stored proceudre either sp_helpindex2 or sp_helpindex3
This is a sample of the output using sp_helpindex2 against the Northwind database:
| Table | Index | Attributes | Columns |
|---|---|---|---|
| Categories | CategoryName | nonclustered located on PRIMARY | CategoryName |
| Categories | PK_Categories | clustered, unique, primary key located on PRIMARY | CategoryID |
| CustomerCustomerDemo | PK_CustomerCustomerDemo | nonclustered, unique, primary key located on PRIMARY | CustomerID, CustomerTypeID |
| CustomerDemographics | PK_CustomerDemographics | nonclustered, unique, primary key located on PRIMARY | CustomerTypeID |
| Customers | City | nonclustered located on PRIMARY | City |
| Customers | CompanyName | nonclustered located on PRIMARY | CompanyName |
| Customers | PK_Customers | clustered, unique, primary key located on PRIMARY | CustomerID |
| Customers | PostalCode | nonclustered located on PRIMARY | PostalCode |
| Customers | Region | nonclustered located on PRIMARY | Region |
| Employees | LastName | nonclustered located on PRIMARY | LastName |
| Employees | PK_Employees | clustered, unique, primary key located on PRIMARY | EmployeeID |
| Employees | PostalCode | nonclustered located on PRIMARY | PostalCode |
| EmployeeTerritories | PK_EmployeeTerritories | unique, primary key located on PRIMARY | EmployeeID, TerritoryID |
| Order Details | OrderID | nonclustered located on PRIMARY | OrderID |
| Order Details | OrdersOrder_Details | nonclustered located on PRIMARY | OrderID |
| Order Details | PK_Order_Details | clustered, unique, primary key located on PRIMARY | OrderID, ProductID |
| Order Details | ProductID | nonclustered located on PRIMARY | ProductID |
| Order Details | ProductsOrder_Details | nonclustered located on PRIMARY | ProductID |
| Orders | CustomerID | nonclustered located on PRIMARY | CustomerID |
| Orders | CustomersOrders | nonclustered located on PRIMARY | CustomerID |
| Orders | EmployeeID | nonclustered located on PRIMARY | EmployeeID |
| Orders | EmployeesOrders | nonclustered located on PRIMARY | EmployeeID |
| Orders | OrderDate | nonclustered located on PRIMARY | OrderDate |
| Orders | PK_Orders | clustered, unique, primary key located on PRIMARY | OrderID |
| Orders | ShippedDate | nonclustered located on PRIMARY | ShippedDate |
| Orders | ShippersOrders | nonclustered located on PRIMARY | ShipVia |
| Orders | ShipPostalCode | nonclustered located on PRIMARY | ShipPostalCode |
| Products | CategoriesProducts | nonclustered located on PRIMARY | CategoryID |
| Products | CategoryID | nonclustered located on PRIMARY | CategoryID |
| Products | PK_Products | lustered, unique, primary key located on PRIMARY | ProductID |
| Products | ProductName | nonclustered located on PRIMARY | ProductName |
| Products | SupplierID | nonclustered located on PRIMARY | SupplierID |
| Products | SuppliersProducts | nonclustered located on PRIMARY | SupplierID |
| Region | PK_Region | nonclustered, unique, primary key located on PRIMARY | RegionID |
| Shippers | PK_Shippers | clustered, unique, primary key located on PRIMARY | ShipperID |
| Suppliers | CompanyName | nonclustered located on PRIMARY | CompanyName |
| Suppliers | PK_Suppliers | clustered, unique, primary key located on PRIMARY | SupplierID |
| Suppliers | PostalCode | nonclustered located on PRIMARY | PostalCode |
| Territories | PK_Territories | nonclustered, unique, primary key located on PRIMARY | TerritoryID |
Next Steps
- Download this stored procedure and put it in your master database so you can use it across all databases
- Refer to this stored procedure whenever you need to do index analysis across your database
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-02-24
