The Ultimate Duplicate Index Finder

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.Util_DupeIndexes', 'P') IS NOT NULL DROP PROCEDURE dbo.Util_DupeIndexes
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_DupeIndexes
By Jesse Roberge - YeshuaAgapao@Yahoo.com
Update - Fixed existance check for drop

Searches database-wide for multiple indexes in the same table which has the same column as the first seek key.  Optionally limits to LIKE wildcard patterns of table and schema names.
Uses XML PATH ('') to pivot the column names.

Required Input Parameters:
none

Optional Input Parameters:
@SchemaName sysname=''Filters schemas.  Can use LIKE wildcards.  All schemas if blank.  Accepts LIKE Wildcards.
@TableName sysname=''Filters tables.  Can use LIKE wildcards.  All tables if blank.  Accepts LIKE Wildcards.

Usage:
EXECUTE Util_DupeIndexes 'dbo', 'hr%'

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/
CREATE PROCEDURE dbo.Util_DupeIndexes
@SchemaName sysname='',
@TableName sysname=''
AS

SELECT
sys.schemas.schema_id, sys.schemas.name AS schema_name,
sys.objects.object_id, sys.objects.name AS object_name,
sys.indexes.index_id, sys.indexes.name AS index_name,
sys.indexes.type, sys.indexes.type_desc,
partitions.Rows, partitions.SizeMB,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN (
SELECT DISTINCT sys.index_columns.object_id, sys.index_columns.index_id
FROM
(
SELECT object_id, column_id
FROM sys.index_columns
WHERE key_ordinal=1 AND is_included_column=0
GROUP BY object_id, column_id
HAVING COUNT(*)>1
) AS index_columns_dupe
JOIN sys.index_columns ON index_columns_dupe.object_id=sys.index_columns.object_id AND index_columns_dupe.column_id=sys.index_columns.column_id AND sys.index_columns.key_ordinal=1
) AS dupe_index_objects ON sys.objects.object_id=dupe_index_objects.object_id
JOIN sys.indexes ON sys.objects.object_id=sys.indexes.object_id AND dupe_index_objects.index_id=sys.indexes.index_id
JOIN (
SELECT
object_id, index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT sys.columns.name + ', '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.name + ', '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE
sys.schemas.name LIKE CASE WHEN @SchemaName='' THEN sys.schemas.name ELSE @SchemaName END
AND sys.objects.name LIKE CASE WHEN @TableName='' THEN sys.objects.name ELSE @TableName END
ORDER BY sys.schemas.name, sys.objects.name, sys.indexes.name
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=