/*
Parameters:
@tblPat sysname = '%'
Name of table. Default is all tables in current database.
@missing_ix tinyint = 1
Whether or not to include missing indexes information. Default is "yes".
Usage examples
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
WARNING:
In order for this to be treated as a regular system procedure we need to create it in the master database and also mark it as a system procedure.
JEOB: Added information for compression and filtered index - 2008 ONLY
*/
USE master
GO
IF OBJECT_ID('sp_indexinfo') IS NOT NULL DROP PROC sp_indexinfo
GO
CREATE PROCEDURE sp_indexinfo
@tblPat sysname = '%'
,@missing_ix tinyint = 0
AS
--Written by Tibor Karaszi 2008-07-07
--Last modified by Tibor Karaszi 2008-07-09
WITH key_columns AS
(
SELECT c.OBJECT_ID, c.name AS column_name, ic.key_ordinal, ic.is_included_column, ic.index_id, ic.is_descending_key
FROM sys.columns AS c with (nolock)
INNER JOIN sys.index_columns AS ic with (nolock) ON c.OBJECT_ID = ic.OBJECT_ID AND ic.column_id = c.column_id
)
, physical_info AS
(
SELECT p.OBJECT_ID, p.index_id, ds.name AS location, SUM(p.rows) AS rows, SUM(a.total_pages) AS pages, p.data_compression_desc as Data_Compression
FROM sys.partitions AS p with (nolock)
INNER JOIN sys.allocation_units AS a with (nolock) ON p.hobt_id = a.container_id
INNER JOIN sys.data_spaces AS ds with (nolock) ON a.data_space_id = ds.data_space_id
GROUP BY OBJECT_ID, index_id, ds.name, p.data_compression_desc
)
SELECT
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS schema_name
,OBJECT_NAME(i.OBJECT_ID) AS table_name
,i.name AS index_name
,CASE i.type WHEN 0 THEN 'heap' WHEN 1 THEN 'cl' WHEN 2 THEN 'nc' WHEN 3 THEN 'xml' ELSE CAST(i.type AS VARCHAR(2)) END AS type
,i.is_unique
,CASE
WHEN is_primary_key = 0 AND is_unique_constraint = 0 THEN 'no'
WHEN is_primary_key = 1 AND is_unique_constraint = 0 THEN 'PK'
WHEN is_primary_key = 0 AND is_unique_constraint = 1 THEN 'UQ'
END
AS cnstr
,(SELECT
CAST(kc.column_name + CASE kc.is_descending_key WHEN 0 THEN '' ELSE ' DESC' END AS VARCHAR(MAX)) + ', ' AS [text()]
FROM key_columns AS kc
WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0
ORDER BY key_ordinal
FOR XML PATH('')
) AS key_columns
,(SELECT CAST(column_name AS VARCHAR(MAX)) + ', ' AS [text()]
FROM key_columns AS kc
WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1
ORDER BY key_ordinal
FOR XML PATH('')
) AS included_columns
,i.filter_definition
,p.location
,p.rows
,p.pages
,CAST((p.pages * 8.00) / 1024 AS decimal(9,2)) AS MB
,p.Data_Compression
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
,STATS_DATE(i.object_id, i.index_id) as Statistics_last_collected
FROM sys.indexes AS i with (nolock)
INNER JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s with (nolock) ON s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
ORDER BY table_name, index_name
IF @missing_ix = 1
BEGIN
SELECT
OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
,OBJECT_NAME(d.OBJECT_ID) AS table_name
,'CREATE INDEX
+ '(' + COALESCE(d.equality_columns, '') + COALESCE(d.inequality_columns, '') + ')'
+ COALESCE(' INCLUDE(' + d.included_columns + ')', '')
AS ddl
,s.user_seeks
,s.user_scans
,s.avg_user_impact
FROM sys.dm_db_missing_index_details AS d with (nolock)
INNER JOIN sys.dm_db_missing_index_groups AS g with (nolock) ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s with (nolock) ON g.index_group_handle = s.group_handle
WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
AND d.database_id = DB_ID()
ORDER BY avg_user_impact DESC
END
GO
EXEC sp_MS_marksystemobject sp_indexinfo
