The Ultimate Duplicate Index Finder

,

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.

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

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

Rate

3 (3)

Share

Share

Rate

3 (3)