/*
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