Overview
I often find myself wanting to know lots about the indexes for a table or for all tables in a database. This is why I wrote sp_indexinfo.
sp_indexinfo.sql
sp_indexinfoAllDBSub.sql, over all databases, by Henrik Staun Poulsen
Versions
SELECT * FROM index_info
ORDER BY MB DESC
SELECT * FROM index_info
WHERE table_name = 'tblname'
SELECT table_name, SUM(MB) AS size
FROM index_info
GROUP BY table_name
ORDER BY size DESC
@tblPat sysname = '%'
@missing_ix tinyint = 0
USE Adventureworks
EXEC sp_indexinfo
EXEC sp_indexinfo 'Product'
EXEC sp_indexinfo 'Product', 0
EXEC sp_indexinfo 'Product%'
EXEC sp_indexinfo DEFAULT, 0
EXEC pubs..sp_indexinfo
Column | Description |
sch_name | Relational schema name. |
tbl_name | Table name. |
ix_name | Index name. |
type | Type of index: heap = datapages for heap table (this is not an index) cl = clustered index cl cs = clustered column index nc = non-clustered index nc cs = non-clustered column index xml = xml index |
uq | 1 if index is unique, else 0. |
cnstr | Whether index is created through constraint empty string = no PK = yes, primary key constraint UQ = yes, unique constraint |
key_cols | Columns in index key. NULL for type = heap. |
incl_cols | Included columns, if any. |
rows | Number of rows in the index. |
pages | Number of pages used. One page = 8KB. |
MB | Number of MB used. |
seeks | Number of seeks by user queries since start of SQL Server. |
scans | Number of scans by user queries since start of SQL Server. |
lookups | Number of bookmark lookups (only for cl ix, lookup from nc index) by user queries since start of SQL Server. |
updates | Number of updates by user queries since start of SQL Server. |
location | What filegroup the index is created on. |
filter | NULL for heap or non-filtered index. |
disabled | 0 if not disabled, 1 if disabled. |
depth | Number of levels in index tree. |
fill_factor | what fill factor the index was created with (not current value). |
page_lock_disallowed | 0 if page lock are allowed, 1 if page locks are not allowed. |
row_lock_disallowed | 0 if row lock are allowed, 1 if row locks are not allowed. |
Column | Description |
schema_name | Relational schema name. |
table_name | Table name. |
ddl | CREATE INDEX statements, generated from the DMVs. |
user_seeks | Number of seeks caused by user queries that the recommended index in the group could have been used for. |
user_scans | Number of scans caused by user queries that the recommended index in the group could have been used for. |
avg_user_impact | Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented. |
Change log
2008-07-08:
Added missing index information controlled by optional parameter.
Added schema name in first resultset.
2008-07-09:
Added 'DESC' for descending keys, tip from Alejandro Mesa.
Added COALESCE for included columns so the whole CREATE INDEX isn't NULL for missing index recommendation where no included columns are recommended. Caught by Aaron Bertrand.
Restricted missing index recommendation to current database. Caught by Aaron Bertrand.
Added parenthesis around key columns in the generated CREATE INDEX command, in the missing indexes resultset.
2008-07-13:
Fixed so that a comma is introduced in the generated CREATE TABLE if both equality as well as inequality columns are recommended. Thanks to Razvan Socol.
2009-02-18:
Made key_columns and included_columns a bit nicer by removing ending ', '.
Included XML indexes. I have not, however considered adding information for space usage for these "indexes". XML indexes are not regular b-tree indexes so space usage info is found elsewhere. And I still want to keep this in one query (not procedural code). Perhaps some slow day I will look at adding spae usage info for XML indexes, but not today. :-)
Thanks to Dejan Sarka for above two tips.
2009-02-18:
Added brief documentation of what the procedure returns.
2009-02-19:
Added filtered index, as suggested by at my blog by Lidong (thanks!).
2010-02-04:
Added explicit text to indicate if index is disabled. Thanks to Henrik Staun Poulsen.
2010-05-24:
Added calls to INDEXPROPERTY to check if index is disabled, depth, fillfactor etc. This new code is commented, remove comments as you wish. Thanks to Stephen Morris.
2010-11-09:
Fixed a bug where row count could be doubled or trippled if LOB data exists and/or row overflow data. Thanks JackMcC, reporing this here.
2014-05-19:
Changed last query to generate the index name. This is based on schema name, object name, and each column name - truncated to 128 characters.
Re-arranged and shortened column names to make output more compact and more commonly used column moved to the right.
Uncommented some columns that were previously commented.
Added support for columnstore indexes.
2016-03-21:
Added compression column, as per Stephen Morris' suggestion.
2018-03-17:
Added support for clustered column store index. Index types are now heap, cl, nc, xml, cl cs and nc cs.Change from 'no' to empty string for the cnstr column if the index isn't a PRIMARY KEY or UNIQUE constraint.
Added ix_id column.