--修改了sp_helpindex SP以显示所有表的所有索引
--已修改为处理dbo和其他用户拥有的对象

CREATE proc sp_helpindex3
	--@objname nvarchar(776)		-- the table to check for indexes
as
	-- PRELIM
	set nocount on

	declare @objname nvarchar(776), 
			@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,
			@usrname 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


	-- create temp table
	create table #spindtab
	(
		
		usr_name			sysname,
		table_name			sysname,		
		index_name			sysname	collate database_default,
		stats				int,
		groupname			sysname collate database_default,
		index_keys			nvarchar(2126)	collate database_default  -- see @keys above for length descr
	)


	-- OPEN CURSOR OVER TABLES (skip stats: bug shiloh_51196)
	declare ms_crs_tab cursor local static for
	select sysobjects.id, sysobjects.name, sysusers.name from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid where type = 'U'

	open ms_crs_tab
	fetch ms_crs_tab into @objid, @objname, @usrname

	while @@fetch_status >= 
	begin


	-- 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, name, status from sysindexes
			where id = @objid and indid >  and indid < 255 and (status & 64)= 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

	-- 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 >= 
	begin
		-- First we'll figure out what the keys are.
		declare @i int, @thiskey nvarchar(131) -- 128+3

		select @keys = index_col(@usrname + '.' + @objname, @indid, 1), @i = 2
		if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
			select @keys = @keys  + '(-)'

		select @thiskey = index_col(@usrname + '.' + @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(@usrname + '.' + @objname, @indid, @i)
			if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
				select @thiskey = @thiskey + '(-)'
		end

		select @groupname = groupname from sysfilegroups where groupid = @groupid

		-- INSERT ROW FOR INDEX
		insert into #spindtab values (@usrname, @objname, @indname, @status, @groupname, @keys)

		-- Next index
		fetch ms_crs_ind into @indid, @groupid, @indname, @status
	end
	deallocate ms_crs_ind

	fetch ms_crs_tab into @objid, @objname, @usrname
	end
	deallocate ms_crs_tab

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

	-- DISPLAY THE RESULTS
	select
		'usr_name'=usr_name,
		'table_name'=table_name,		
		'index_name' = index_name,
		'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
				case when (stats & 16)<> then 'clustered' else 'nonclustered' end
				+ case when (stats & 1)<> then ', '+@des1 else @empty end
				+ case when (stats & 2)<> then ', '+@des2 else @empty end
				+ case when (stats & 4)<> then ', '+@des4 else @empty end
				+ case when (stats & 64)<> then ', '+@des64 else case when (stats & 32)<> then ', '+@des32 else @empty end end
				+ case when (stats & 2048)<> then ', '+@des2048 else @empty end
				+ case when (stats & 4096)<> then ', '+@des4096 else @empty end
				+ case when (stats & 8388608)<> then ', '+@des8388608 else @empty end
				+ case when (stats & 16777216)<> then ', '+@des16777216 else @empty end
				+ ' located on ' + groupname),
		'index_keys' = index_keys
	from #spindtab
	order by table_name, index_name


	return () -- sp_helpindex

GO