sp_helpindex2

Girish-Sumaria, 2011-03-18 (first published: 2010-09-21)

Execute the complete script after connecting to SSMS. Ensure you have SYSADMIN priviledges.

 

Once done, you can use sp_helpindex2 instead of sp_helpindex.

    use master 
go
alter procedure dbo.sp_helpindex2
	@objname nvarchar(776)		-- the table to check for indexes
as
/*
Original Script - Microsoft Corporation

Modified by - Girish Sumaria - girish.sumaria@gmail.com

Information - The sp_helpindex only provides the list of columns in the index and not the INCLUDED COLUMNS.
		In order to retrieve complete index information, I have modified the original code so that 
		you can now retrieve INCLUDED COLUMNS list as well. Also, the index type can be retrived separately.
		
Tip from my fellow Database Developer - Prasant Nanda - prasant.nanda@gmail.com
		Ensure that you mark this procedure as a SYSTEM PROCEDURE to leverage its use from all databases. 

*/

	-- PRELIM
	set nocount on

	declare @objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@groupid int,  		-- the filegroup id of an index
			@indname sysname,
			@groupname sysname,
			@status int,
			@keys nvarchar(2126),	--Length (16*max_identifierLength)+(15*2)+(16*3)
			@included_keys nvarchar(2126),	--Length (16*max_identifierLength)+(15*2)+(16*3)
			@InclCol nvarchar(225),
			@dbname	sysname,
			@ignore_dup_key	bit,
			@is_unique		bit,
			@is_hypothetical	bit,
			@is_primary_key	bit,
			@is_unique_key 	bit,
			@auto_created	bit,
			@no_recompute	bit

	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3)
--	print @dbname 
	if @dbname is null
		select @dbname = db_name()
	else if @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)
--	print @objid 
	if @objid is NULL
	begin
		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 i.index_id, i.data_space_id, i.name,
			i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
			s.auto_created, s.no_recompute
		from sys.indexes i join sys.stats s
			on i.object_id = s.object_id and i.index_id = s.stats_id
		where i.object_id = @objid
	open ms_crs_ind
	fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute

	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ms_crs_ind
		raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
		return (0)
	end

	-- create temp table
	CREATE TABLE #spindtab
	(
		index_name			sysname	collate database_default NOT NULL,
		index_id				int,
		ignore_dup_key		bit,
		is_unique				bit,
		is_hypothetical		bit,
		is_primary_key		bit,
		is_unique_key			bit,
		auto_created			bit,
		no_recompute			bit,
		groupname			sysname collate database_default NULL,
		index_keys			nvarchar(2126)	collate database_default NOT NULL, -- see @keys above for length descr
		included_keys			nvarchar(2126)	collate database_default NULL
	)

	-- 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 = index_col(@objname, @indid, 1), @i = 2
		if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
			select @keys = @keys  + '(-)'

		select @thiskey = index_col(@objname, @indid, @i)
		if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
			select @thiskey = @thiskey + '(-)'

		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @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 + '(-)'
		end

/* Code to find Included Columns goes here */
	set @InclCol=null
	set @included_keys=null 
	declare ms_crs_inc_cols cursor local static for
		SELECT --sys.tables.object_id, 
			--sys.tables.name as table_name, sys.indexes.name as index_name,sys.indexes.type_desc as Ind_Type, 
		sys.columns.name as column_name
		--,sys.index_columns.index_column_id, sys.indexes.is_unique, sys.indexes.is_primary_key , sys.index_columns.is_included_column 
		FROM sys.tables, sys.indexes, sys.index_columns, sys.columns 
		WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
		AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id) 
		AND sys.indexes.object_id = @objid
			and sys.indexes.index_id = @indid
			and is_included_column=1
		order by index_column_id
	open ms_crs_inc_cols 
	fetch next from ms_crs_inc_cols into @InclCol
	while @@fetch_status >= 0
	begin
		if @included_keys is null
			set @included_keys=@InclCol
		else
			set @included_keys=@included_keys+','+@InclCol
		print @included_keys
		fetch next from ms_crs_inc_cols into @InclCol
	end
	close ms_crs_inc_cols
	deallocate ms_crs_inc_cols

/* Code to find Included Columns ends here */

		select @groupname = null
		select @groupname = name from sys.data_spaces where data_space_id = @groupid

		-- INSERT ROW FOR INDEX
		insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys,@included_keys)

		-- Next index
		fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute
	end
	deallocate ms_crs_ind

	-- DISPLAY THE RESULTS
	select
		'index_name' = index_name,
		'type' = case when index_id = 1 then 'clustered' else 'nonclustered' end,
		'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
				case when ignore_dup_key <>0 then 'ignore duplicate keys' else '' end
				+ case when is_unique <>0 then ', unique' else '' end
				+ case when is_hypothetical <>0 then ', hypothetical' else '' end
				+ case when is_primary_key <>0 then ', primary key' else '' end
				+ case when is_unique_key <>0 then ', unique key' else '' end
				+ case when auto_created <>0 then ', auto create' else '' end
				+ case when no_recompute <>0 then ', stats no recompute' else '' end
				+ ' located on ' + groupname),
		'index_keys' = index_keys,
		'included_keys' = included_keys
	from #spindtab
	order by index_name


	return (0) -- sp_helpindex

go
USE Master
EXEC sp_MS_marksystemobject sp_helpindex2
go

Rate

3.25 (4)

Share

Share

Rate

3.25 (4)

Related content

SQL Server Indexes: The Basics

Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.

Additional Articles

2007-12-04

5,686 reads

Indexed Views With Outer Joins

SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.

3.5 (6)

Jean Bulinckx

2005-05-19

14,085 reads

Clustering for Indexes

There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.

Additional Articles

2004-12-23

3,412 reads