SP_INDEXINFO

http://www.karaszi.com/sqlserver/util_sp_indexinfo.asp


/*
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 ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' '
 + '(' + 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