Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Index Analysis Script for All Indexes on All Tables


By:   |   Read Comments (5)   |   Related Tips: More > 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:

  • Enterprise Manager
    • Database -> Tables -> All Tasks -> Manage Indexes
    • The problem with this approach is that you have to look at one table and one index 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 Query Analyzer and run the script
  • in Query Analyzer change to the database you want to analyze
  • execute sp_helpindex2

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


Last Update:






About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, May 23, 2013 - 3:22:39 PM - Greg Robidoux Back To Top

@Jean Luc

take a look at this tip to see if this works for you:  http://www.mssqltips.com/sqlservertip/1545/deeper-insight-into-unused-indexes-for-sql-server/

 

I have not tried the above with SQL 2012 yet, but this other tip should work.


Thursday, May 23, 2013 - 1:16:35 PM - Jean Luc Ravenne Back To Top

Hello from France

I'm sorry but.... :

 

After executing the script :

The object 'trace_xe_action_map' does not exist in database 'master'

or is invalid for this operation.

I work on SQL 2012

any solution ?

 

many thanks


Tuesday, February 26, 2013 - 9:14:27 AM - Naimish Back To Top

plz give me a General Query or function for retrieveing below things related to microsoft sql server 2000 or more versions....

i want

schema,table name,view ,column count,primary key,primary key schema,composite primary key,foreign key,unique key count,identity column,table size ,remarks,row count,primary key count,foreign key count,foreign key column count,primary key column count,unique key count,unique key column count,computed column count,identity column count

 

 

 


Wednesday, January 09, 2013 - 12:52:49 PM - perl Back To Top

Genius

 


Thursday, May 29, 2008 - 9:41:58 AM - WileECoyote Back To Top

Good script, one slight issue.  The filegroup reported for the indexes is not always correct.

I have created a filegroup (INDEX) and moved indexes into it for some of my databases. 

 The sp_helpindex3 script reports that all indexes are on filegroup PRIMARY, even when they are not.

 The script gives me a starting point to discover which indexes have been moved and which have not, so I appreciate the efforts. :-)


Learn more about SQL Server tools