Skip to content

Commit 5f11d2e

Browse files
rabrystrabryst
authored
Moved to GitHub
1 parent 99baba3 commit 5f11d2e

1 file changed

Lines changed: 371 additions & 0 deletions

File tree

‎sp_SQLskills_SQL2000_helpindex.sql‎

Lines changed: 371 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,371 @@
1+
/*============================================================================
2+
File: sp_SQLskills_SQL2000_helpindex.sql
3+
4+
Summary: Based on sp_helpindex from SQL Server 2000, this script outputs
5+
an index structure in the same format as the 2005+ script by
6+
Kimberly Tripp
7+
8+
See: https://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/
9+
See also: http://bornsql.ca/s/script-duplicate-index-finder
10+
11+
This script is called by sp_SQLskills_SQL2000_finddupes
12+
13+
Date: 29 September 2017
14+
15+
SQL Server Versions:
16+
SQL Server 2000
17+
------------------------------------------------------------------------------
18+
Written by Randolph West, bornsql.ca
19+
Based on scripts developed by Kimberly L. Tripp, SQLSkills.com.
20+
21+
Copyright (c) Born SQL.
22+
23+
You may alter this code for your own *non-commercial* purposes. You may
24+
republish altered code as long as you include this copyright and give due
25+
credit, but you must obtain prior permission before blogging this code.
26+
27+
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
28+
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
29+
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
30+
PARTICULAR PURPOSE.
31+
============================================================================*/
32+
33+
USE master
34+
GO
35+
36+
IF OBJECTPROPERTY(OBJECT_ID('sp_SQLskills_SQL2000_helpindex'), 'IsProcedure') = 1
37+
DROP PROCEDURE sp_SQLskills_SQL2000_helpindex
38+
GO
39+
40+
SET ANSI_NULLS ON
41+
GO
42+
43+
SET QUOTED_IDENTIFIER ON
44+
GO
45+
46+
CREATE PROCEDURE [dbo].[sp_SQLskills_SQL2000_helpindex] @objname NVARCHAR(776) -- the table to check for indexes
47+
AS
48+
-- September 2013: Moved to GitHub.
49+
-- May 2013: Worked around RID / UNIQUIFIER not displaying correctly.
50+
-- August 2012: Updated copyright bits, cleaned up formatting and
51+
-- comments.
52+
53+
-- March 2012: Based on SQL Server 2000 sp_helpindex with revised
54+
-- code for columns in index levels.
55+
56+
-- For the original script, updates and/or additional information, see
57+
-- http://www.SQLskills.com/blogs/Kimberly (Kimberly L. Tripp)
58+
59+
-- For updates to this port of the script, see
60+
-- http://BornSQL.ca (Randolph West)
61+
62+
-- PRELIM
63+
SET NOCOUNT ON
64+
65+
DECLARE @objid INT, -- the object id of the table
66+
@indid SMALLINT, -- the index id of an index
67+
@groupid SMALLINT, -- the filegroup id of an index
68+
@indname SYSNAME,
69+
@groupname SYSNAME,
70+
@status INT,
71+
@keys NVARCHAR(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
72+
@dbname SYSNAME
73+
74+
-- Check to see that the object names are local to the current database.
75+
SELECT @dbname = PARSENAME(@objname, 3)
76+
77+
IF @dbname IS NOT NULL
78+
AND @dbname <> DB_NAME()
79+
BEGIN
80+
RAISERROR (15250, -1, -1)
81+
RETURN (1)
82+
END
83+
84+
-- Check to see the the table exists and initialize @objid.
85+
SELECT @objid = OBJECT_ID(@objname)
86+
87+
IF @objid IS NULL
88+
BEGIN
89+
SELECT @dbname = DB_NAME()
90+
RAISERROR (15009, -1, -1, @objname, @dbname)
91+
RETURN (1)
92+
END
93+
94+
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
95+
DECLARE ms_crs_ind CURSOR LOCAL STATIC
96+
FOR
97+
SELECT [indid],
98+
[groupid],
99+
QUOTENAME(NAME, N']'),
100+
[status]
101+
FROM sysindexes
102+
WHERE [id] = @objid
103+
AND [indid] > 0
104+
AND [indid] < 255
105+
AND ([status] & 64) = 0
106+
ORDER BY indid
107+
108+
OPEN ms_crs_ind
109+
110+
FETCH ms_crs_ind
111+
INTO @indid,
112+
@groupid,
113+
@indname,
114+
@status
115+
116+
-- IF NO INDEX, QUIT
117+
IF @@FETCH_STATUS < 0
118+
BEGIN
119+
DEALLOCATE ms_crs_ind
120+
RAISERROR (15472, -1, -1) --'Object does not have any indexes.'
121+
RETURN (0)
122+
END
123+
124+
-- create temp table
125+
CREATE TABLE #spindtab (
126+
[index_id] INT,
127+
[index_name] SYSNAME COLLATE database_default NOT NULL,
128+
[stats] INT,
129+
[groupname] SYSNAME COLLATE database_default NOT NULL,
130+
[index_keys] NVARCHAR(2126) COLLATE database_default NOT NULL -- see @keys above for length descr
131+
)
132+
133+
-- Now check out each index, figure out its type and keys and
134+
-- save the info in a temporary table that we'll print out at the end.
135+
WHILE @@FETCH_STATUS >= 0
136+
BEGIN
137+
-- First we'll figure out what the keys are.
138+
DECLARE @i INT,
139+
@thiskey NVARCHAR(131) -- 128+3
140+
141+
SELECT @keys = QUOTENAME(INDEX_COL(@objname, @indid, 1), N']'),
142+
@i = 2
143+
144+
IF (INDEXKEY_PROPERTY(@objid, @indid, 1, 'IsDescending') = 1)
145+
SELECT @keys = @keys + '(-)'
146+
147+
SELECT @thiskey = INDEX_COL(@objname, @indid, @i)
148+
149+
IF (
150+
(@thiskey IS NOT NULL)
151+
AND (INDEXKEY_PROPERTY(@objid, @indid, @i, 'IsDescending') = 1)
152+
)
153+
SELECT @thiskey = @thiskey + '(-)'
154+
155+
WHILE (@thiskey IS NOT NULL)
156+
BEGIN
157+
SELECT @keys = @keys + ', ' + QUOTENAME(@thiskey, N']'),
158+
@i = @i + 1
159+
160+
SELECT @thiskey = INDEX_COL(@objname, @indid, @i)
161+
162+
IF (
163+
(@thiskey IS NOT NULL)
164+
AND (INDEXKEY_PROPERTY(@objid, @indid, @i, 'IsDescending') = 1)
165+
)
166+
SELECT @thiskey = @thiskey + '(-)'
167+
END
168+
169+
SELECT @groupname = groupname
170+
FROM sysfilegroups
171+
WHERE groupid = @groupid
172+
173+
-- INSERT ROW FOR INDEX
174+
INSERT INTO #spindtab
175+
VALUES (
176+
@indid,
177+
@indname,
178+
@status,
179+
@groupname,
180+
@keys
181+
)
182+
183+
-- Next index
184+
FETCH ms_crs_ind
185+
INTO @indid,
186+
@groupid,
187+
@indname,
188+
@status
189+
END
190+
191+
DEALLOCATE ms_crs_ind
192+
193+
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
194+
DECLARE @empty VARCHAR(1)
195+
196+
SELECT @empty = ''
197+
198+
DECLARE @des1 VARCHAR(35), -- 35 matches spt_values
199+
@des2 VARCHAR(35),
200+
@des4 VARCHAR(35),
201+
@des32 VARCHAR(35),
202+
@des64 VARCHAR(35),
203+
@des2048 VARCHAR(35),
204+
@des4096 VARCHAR(35),
205+
@des8388608 VARCHAR(35),
206+
@des16777216 VARCHAR(35)
207+
208+
SELECT @des1 = [name]
209+
FROM master.dbo.spt_values
210+
WHERE [type] = 'I'
211+
AND [number] = 1
212+
213+
SELECT @des2 = [name]
214+
FROM master.dbo.spt_values
215+
WHERE [type] = 'I'
216+
AND [number] = 2
217+
218+
SELECT @des4 = [name]
219+
FROM master.dbo.spt_values
220+
WHERE [type] = 'I'
221+
AND [number] = 4
222+
223+
SELECT @des32 = [name]
224+
FROM master.dbo.spt_values
225+
WHERE [type] = 'I'
226+
AND [number] = 32
227+
228+
SELECT @des64 = [name]
229+
FROM master.dbo.spt_values
230+
WHERE [type] = 'I'
231+
AND [number] = 64
232+
233+
SELECT @des2048 = [name]
234+
FROM master.dbo.spt_values
235+
WHERE [type] = 'I'
236+
AND [number] = 2048
237+
238+
SELECT @des4096 = [name]
239+
FROM master.dbo.spt_values
240+
WHERE [type] = 'I'
241+
AND [number] = 4096
242+
243+
SELECT @des8388608 = [name]
244+
FROM master.dbo.spt_values
245+
WHERE [type] = 'I'
246+
AND [number] = 8388608
247+
248+
SELECT @des16777216 = [name]
249+
FROM master.dbo.spt_values
250+
WHERE [type] = 'I'
251+
AND [number] = 16777216
252+
253+
-- Simple workaround to establish the tree-level columns included
254+
DECLARE @clustered_index NVARCHAR(4000)
255+
DECLARE @uniquifier BIT
256+
257+
SELECT @clustered_index = ISNULL(index_keys, 'RID'),
258+
@uniquifier = CASE
259+
WHEN index_keys IS NOT NULL
260+
AND (stats & 2) = 0
261+
THEN 1
262+
ELSE 0
263+
END
264+
FROM #spindtab
265+
WHERE index_id = 1
266+
267+
-- DISPLAY THE RESULTS
268+
--(stats & 1) <> 0 = ignore_duplicate_keys
269+
--(stats & 2) <> 0 = unique
270+
--(stats & 4) <> 0 = ignore_duplicate_rows
271+
--(stats & 16) <> 0 = clustered
272+
--(stats & 32) <> 0 = hypothetical
273+
--(stats & 64) <> 0 statistics
274+
--(stats & 2048) <> 0 = primary_key
275+
--(stats & 4096) <> 0 = unique_key
276+
--(stats & 8388608) <> 0 = auto_create
277+
--(stats & 16777216) <> 0 = stats_no_recompute
278+
279+
SELECT index_id,
280+
index_name,
281+
CONVERT(VARCHAR(210), --bits 16 off, 1, 2, 16777216 on, located on group
282+
CASE
283+
WHEN (stats & 16) <> 0
284+
THEN 'clustered'
285+
ELSE 'nonclustered'
286+
END + CASE
287+
WHEN (stats & 1) <> 0
288+
THEN ', ' + @des1
289+
ELSE @empty
290+
END + CASE
291+
WHEN (stats & 2) <> 0
292+
THEN ', ' + @des2
293+
ELSE @empty
294+
END + CASE
295+
WHEN (stats & 4) <> 0
296+
THEN ', ' + @des4
297+
ELSE @empty
298+
END + CASE
299+
WHEN (stats & 64) <> 0
300+
THEN ', ' + @des64
301+
ELSE CASE
302+
WHEN (stats & 32) <> 0
303+
THEN ', ' + @des32
304+
ELSE @empty
305+
END
306+
END + CASE
307+
WHEN (stats & 2048) <> 0
308+
THEN ', ' + @des2048
309+
ELSE @empty
310+
END + CASE
311+
WHEN (stats & 4096) <> 0
312+
THEN ', ' + @des4096
313+
ELSE @empty
314+
END + CASE
315+
WHEN (stats & 8388608) <> 0
316+
THEN ', ' + @des8388608
317+
ELSE @empty
318+
END + CASE
319+
WHEN (stats & 16777216) <> 0
320+
THEN ', ' + @des16777216
321+
ELSE @empty
322+
END + ' located on ' + groupname) AS [index_description],
323+
index_keys,
324+
CASE
325+
WHEN (stats & 2) = 0 /*non unique*/
326+
AND (stats & 16) = 0 /*non-clustered*/
327+
AND @uniquifier = 0
328+
THEN index_keys + ', ' + ISNULL(@clustered_index, 'RID')
329+
WHEN (stats & 2) = 0 /*non unique*/
330+
AND (stats & 16) = 0 /*non-clustered*/
331+
AND @uniquifier = 1
332+
THEN index_keys + ', ' + ISNULL(@clustered_index, 'RID') + ', UNIQUIFIER'
333+
WHEN (stats & 2) = 0 /*non unique*/
334+
AND (stats & 16) <> 0 /*clustered*/
335+
THEN index_keys + ', UNIQUIFIER'
336+
WHEN @clustered_index IS NOT NULL
337+
AND @uniquifier = 0
338+
THEN index_keys
339+
ELSE index_keys + ', RID'
340+
END AS [columns_in_tree],
341+
CASE
342+
WHEN (stats & 2048) <> 0
343+
THEN 'All columns "included" - the leaf level IS the data row.'
344+
WHEN (stats & 4096) <> 0
345+
THEN index_keys + ', ' + @clustered_index
346+
WHEN (stats & 2) = 0 /*non unique*/
347+
AND (stats & 16) = 0 /*non-clustered*/
348+
AND @uniquifier = 0
349+
THEN index_keys + ', ' + ISNULL(@clustered_index, 'RID')
350+
WHEN (stats & 2) = 0 /*non unique*/
351+
AND (stats & 16) = 0 /*non-clustered*/
352+
AND @uniquifier = 1
353+
THEN index_keys + ', ' + ISNULL(@clustered_index, 'RID') + ', UNIQUIFIER'
354+
WHEN (stats & 2) = 0 /*non unique*/
355+
AND (stats & 16) <> 0 /*clustered*/
356+
THEN 'All columns "included" - the leaf level IS the data row.'
357+
WHEN (stats & 2) <> 0 /*unique*/
358+
AND (stats & 16) = 0 /*non-clustered*/
359+
THEN index_keys
360+
ELSE index_keys + ', RID'
361+
END AS [columns_in_leaf]
362+
FROM #spindtab
363+
ORDER BY index_id
364+
365+
DROP TABLE #spindtab
366+
367+
RETURN (0) -- sp_helpindex
368+
GO
369+
370+
EXEC sp_MS_marksystemobject 'dbo.sp_SQLskills_SQL2000_helpindex'
371+
GO

0 commit comments

Comments
 (0)