sp_SQL2000_helpindex.sql

/*
	sp_SQL2000_helpindex.sql
	https://bornsql.ca/s/script-duplicate-index-finder/
	Copyright (c) BornSQL.ca
	Written by Randolph West, released under the MIT License
	Last updated: 19 June 2020

	Based on sp_helpindex from SQL Server 2000, this script outputs
	an index structure in the same format as the 2005+ script by
	Kimberly Tripp. This is written specifically for SQL Server 2000.

	This script is called by sp_SQL2000_finddupes

	See: https://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/

	-- June 2020: Renamed files, fixed formatting, and improved comments.

	-- September 2013: Moved to GitHub.

	-- May 2013: Worked around RID / UNIQUIFIER not displaying correctly.

	-- August 2012: Updated copyright bits, cleaned up formatting and
	--			    comments.

	--  March 2012: Based on SQL Server 2000 sp_helpindex with revised
	--			    code for columns in index levels.
*/

USE master
GO

IF OBJECTPROPERTY(OBJECT_ID('sp_SQL2000_helpindex'), 'IsProcedure') = 1
	DROP PROCEDURE sp_SQL2000_helpindex
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_SQL2000_helpindex] @objname NVARCHAR(776) -- the table to check for indexes
AS
BEGIN

	-- PRELIM
	SET NOCOUNT ON

	DECLARE @objid INT,		   -- the object id of the table
			@indid SMALLINT,	  -- the index id of an index
			@groupid SMALLINT,	-- the filegroup id of an index
			@indname sysname,
			@groupname sysname,
			@status INT,
			@keys NVARCHAR(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
			@dbname sysname

	-- Check to see that the object names are local to the current database.
	SELECT @dbname = PARSENAME(@objname, 3)

	IF @dbname IS NOT NULL
	   AND @dbname <> DB_NAME()
	BEGIN
		RAISERROR(15250, -1, -1)
		RETURN (1)
	END

	-- Check to see the the table exists and initialize @objid.
	SELECT @objid = OBJECT_ID(@objname)

	IF @objid IS NULL
	BEGIN
		SELECT @dbname = DB_NAME()
		RAISERROR(15009, -1, -1, @objname, @dbname)
		RETURN (1)
	END

	-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
	DECLARE ms_crs_ind CURSOR LOCAL STATIC FOR
	SELECT [indid],
		   [groupid],
		   QUOTENAME(name, N']'),
		   [status]
	FROM sysindexes
	WHERE [id] = @objid
		  AND [indid] > 0
		  AND [indid] < 255
		  AND ([status] & 64) = 0
	ORDER BY indid

	OPEN ms_crs_ind

	FETCH ms_crs_ind
	INTO @indid,
		 @groupid,
		 @indname,
		 @status

	-- IF NO INDEX, QUIT
	IF @@FETCH_STATUS < 0
	BEGIN
		DEALLOCATE ms_crs_ind
		RAISERROR(15472, -1, -1) --'Object does not have any indexes.'
		RETURN (0)
	END

	-- create temp table
	CREATE TABLE #spindtab
	(
		[index_id] INT NOT NULL,
		[index_name] sysname COLLATE DATABASE_DEFAULT NOT NULL,
		[stats] INT NULL,
		[groupname] sysname COLLATE DATABASE_DEFAULT NOT NULL,
		[index_keys] NVARCHAR(2126) COLLATE DATABASE_DEFAULT NOT NULL -- see @keys above for length descr
	)

	-- Now check out each index, figure out its type and keys and
	--	save the info in a temporary table that we'll print out at the end.
	WHILE @@FETCH_STATUS >= 0
	BEGIN
		-- First we'll figure out what the keys are.
		DECLARE @i INT,
				@thiskey NVARCHAR(131) -- 128+3

		SELECT @keys = QUOTENAME(INDEX_COL(@objname, @indid, 1), N']'),
			   @i  = 2

		IF (INDEXKEY_PROPERTY(@objid, @indid, 1, 'IsDescending') = 1)
			SELECT @keys = @keys + N'(-)'

		SELECT @thiskey = INDEX_COL(@objname, @indid, @i)

		IF (
			   (@thiskey IS NOT NULL)
			   AND (INDEXKEY_PROPERTY(@objid, @indid, @i, 'IsDescending') = 1)
		   )
			SELECT @thiskey = @thiskey + N'(-)'

		WHILE (@thiskey IS NOT NULL)
		BEGIN
			SELECT @keys = @keys + N', ' + QUOTENAME(@thiskey, N']'),
				   @i  = @i + 1

			SELECT @thiskey = INDEX_COL(@objname, @indid, @i)

			IF (
				   (@thiskey IS NOT NULL)
				   AND (INDEXKEY_PROPERTY(@objid, @indid, @i, 'IsDescending') = 1)
			   )
				SELECT @thiskey = @thiskey + N'(-)'
		END

		SELECT @groupname = groupname
		FROM sysfilegroups
		WHERE groupid = @groupid

		-- INSERT ROW FOR INDEX
		INSERT INTO #spindtab
		VALUES
		(@indid, @indname, @status, @groupname, @keys)

		-- Next index
		FETCH ms_crs_ind
		INTO @indid,
			 @groupid,
			 @indname,
			 @status
	END

	DEALLOCATE ms_crs_ind

	-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
	DECLARE @empty VARCHAR(1)

	SELECT @empty = ''

	DECLARE @des1 VARCHAR(35), -- 35 matches spt_values
			@des2 VARCHAR(35),
			@des4 VARCHAR(35),
			@des32 VARCHAR(35),
			@des64 VARCHAR(35),
			@des2048 VARCHAR(35),
			@des4096 VARCHAR(35),
			@des8388608 VARCHAR(35),
			@des16777216 VARCHAR(35)

	SELECT @des1 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 1

	SELECT @des2 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 2

	SELECT @des4 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 4

	SELECT @des32 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 32

	SELECT @des64 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 64

	SELECT @des2048 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 2048

	SELECT @des4096 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 4096

	SELECT @des8388608 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 8388608

	SELECT @des16777216 = [name]
	FROM master.dbo.spt_values
	WHERE [type] = 'I'
		  AND [number] = 16777216

	-- Simple workaround to establish the tree-level columns included
	DECLARE @clustered_index NVARCHAR(4000)
	DECLARE @uniquifier BIT

	SELECT @clustered_index = ISNULL(index_keys, 'RID'),
		   @uniquifier = CASE
							 WHEN index_keys IS NOT NULL
								  AND (stats & 2) = 0 THEN
								 1
							 ELSE
								 0
						 END
	FROM #spindtab
	WHERE index_id = 1

	-- DISPLAY THE RESULTS
	--(stats & 1) <> 0 = ignore_duplicate_keys
	--(stats & 2) <> 0 = unique
	--(stats & 4) <> 0 = ignore_duplicate_rows
	--(stats & 16) <> 0 = clustered
	--(stats & 32) <> 0 = hypothetical
	--(stats & 64) <> 0 statistics
	--(stats & 2048) <> 0 = primary_key
	--(stats & 4096) <> 0 = unique_key
	--(stats & 8388608) <> 0 = auto_create
	--(stats & 16777216) <> 0 = stats_no_recompute

	SELECT index_id,
		   index_name,
		   CONVERT(   VARCHAR(210), --bits 16 off, 1, 2, 16777216 on, located on group
					  CASE
						  WHEN (stats & 16) <> 0 THEN
							  'clustered'
						  ELSE
							  'nonclustered'
					  END + CASE
								WHEN (stats & 1) <> 0 THEN
									', ' + @des1
								ELSE
									@empty
							END + CASE
									  WHEN (stats & 2) <> 0 THEN
										  ', ' + @des2
									  ELSE
										  @empty
								  END + CASE
											WHEN (stats & 4) <> 0 THEN
												', ' + @des4
											ELSE
												@empty
										END + CASE
												  WHEN (stats & 64) <> 0 THEN
													  ', ' + @des64
												  ELSE
													  CASE
														  WHEN (stats & 32) <> 0 THEN
															  ', ' + @des32
														  ELSE
															  @empty
													  END
											  END + CASE
														WHEN (stats & 2048) <> 0 THEN
															', ' + @des2048
														ELSE
															@empty
													END + CASE
															  WHEN (stats & 4096) <> 0 THEN
																  ', ' + @des4096
															  ELSE
																  @empty
														  END + CASE
																	WHEN (stats & 8388608) <> 0 THEN
																		', ' + @des8388608
																	ELSE
																		@empty
																END + CASE
																		  WHEN (stats & 16777216) <> 0 THEN
																			  ', ' + @des16777216
																		  ELSE
																			  @empty
																	  END + ' located on ' + groupname
				  ) AS [index_description],
		   index_keys,
		   CASE
			   WHEN (stats & 2) = 0 /*non unique*/
					AND (stats & 16) = 0 /*non-clustered*/
					AND @uniquifier = 0 THEN
				   index_keys + ', ' + ISNULL(@clustered_index, 'RID')
			   WHEN (stats & 2) = 0 /*non unique*/
					AND (stats & 16) = 0 /*non-clustered*/
					AND @uniquifier = 1 THEN
				   index_keys + ', ' + ISNULL(@clustered_index, 'RID') + ', UNIQUIFIER'
			   WHEN (stats & 2) = 0 /*non unique*/
					AND (stats & 16) <> 0 /*clustered*/
		   THEN
				   index_keys + ', UNIQUIFIER'
			   WHEN @clustered_index IS NOT NULL
					AND @uniquifier = 0 THEN
				   index_keys
			   ELSE
				   index_keys + ', RID'
		   END AS [columns_in_tree],
		   CASE
			   WHEN (stats & 2048) <> 0 THEN
				   'All columns "included" - the leaf level IS the data row.'
			   WHEN (stats & 4096) <> 0 THEN
				   index_keys + ', ' + @clustered_index
			   WHEN (stats & 2) = 0 /*non unique*/
					AND (stats & 16) = 0 /*non-clustered*/
					AND @uniquifier = 0 THEN
				   index_keys + ', ' + ISNULL(@clustered_index, 'RID')
			   WHEN (stats & 2) = 0 /*non unique*/
					AND (stats & 16) = 0 /*non-clustered*/
					AND @uniquifier = 1 THEN
				   index_keys + ', ' + ISNULL(@clustered_index, 'RID') + ', UNIQUIFIER'
			   WHEN (stats & 2) = 0 /*non unique*/
					AND (stats & 16) <> 0 /*clustered*/
		   THEN
				   'All columns "included" - the leaf level IS the data row.'
			   WHEN (stats & 2) <> 0 /*unique*/
					AND (stats & 16) = 0 /*non-clustered*/
		   THEN
				   index_keys
			   ELSE
				   index_keys + ', RID'
		   END AS [columns_in_leaf]
	FROM #spindtab
	ORDER BY index_id

	DROP TABLE #spindtab

	RETURN (0) -- sp_helpindex
END
GO

EXEC sp_MS_marksystemobject 'dbo.sp_SQL2000_helpindex'
GO