/*

	Author	: Vijay Koranki
	Date	:30 August 2001
	Ver	:1.4



	 This script queries a given database and gets the indexes for all non system
tables in the database and stores the information in a new table Index_Information in the same
database, and stores the information on which columns in the tables are indexed in a new table
Coulmns_Indexed. 

	It uses a modified form of sp_helpindex to extract index information from the
sys tables.

	The tables Index_Information and Columns_Indexed are dropped and recreated with
each run, so the information from the previous runs is deleted.

     ***  Edit line 28 to use the correct database. ***


*/


----------------------- first get the correct database -----------------------------

USE	Northwind -- ****  enter db name here  ***

GO
--------------------------- drop tables etc  ----------------------------------------
PRINT 'Creating Tables'

IF EXISTS(SELECT * FROM sysobjects WHERE name ='Index_Information' AND type ='U')
	DROP TABLE Index_Information

CREATE TABLE Index_Information	(tablename		varchar(128) NOT NULL,
				 index_name		varchar(128) NOT NULL,
				 index_description	varchar(210) NULL,
				 Index_keys		varchar(2048) NULL)

if exists(select * from sysobjects where name = 'Columns_Indexed' and type = 'U')
	drop table Columns_Indexed


create table Columns_Indexed (Table_Name varchar(128) not null,
				Column_Name varchar(128) not null,
				Indexed	char(1) not null default 'N')
GO
---- get the columns information into the table created -------------------------
print 'Inserting Column Information'

insert into Columns_Indexed ( Table_Name,
				Column_Name)

select sysobjects.name,	syscolumns.name from sysobjects inner join syscolumns on

sysobjects.id = syscolumns.id where sysobjects.type = 'U' and

(objectproperty(sysobjects.id, 'ISMSShipped') = 0) and

sysobjects.name <> 'dtproperties'

GO
------------------------- create a temp procedure for inserting indexes  -------------------
PRINT 'Creating Temp Procedure'
GO
CREATE PROCEDURE #Index_Info (@objname VARCHAR(128))

	AS

	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(2078),-- string build index key list, length = (16*max_id_length)+(15*2)
			@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 
	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 
	end

	-- OPEN CURSOR OVER INDEXES
	declare ind_curs insensitive cursor for
		select indid, groupid, name, status from sysindexes
			where id = @objid and indid > 0 and indid < 255 order by indid
	open ind_curs
	fetch ind_curs into @indid, @groupid, @indname, @status

	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ind_curs
		return 
	end

	-- create temp table
	create table #spindtab
	(
		index_name			sysname	NOT NULL,
		stats				int,
		groupname			sysname NOT NULL,
		index_keys			nvarchar(2078)	NOT NULL
	)

	-- Now check out each index, figure out its type and keys and
	--	save the info in a temporary table that we'll use to insert into Index_Information

	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.
		declare @i int, @thiskey sysname
		select @keys = index_col(@objname, @indid, 1),
				@i = 2, @thiskey = index_col(@objname, @indid, 2)
		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = index_col(@objname, @indid, @i)
		end

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

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

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

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

	-- Insert the results into the table

insert into Index_Information (tablename,
				index_name,
				index_description,
				index_keys)
	select 	replace(right(@objname,(len(@objname)-7)),']',''),
		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),
		index_keys
	from #spindtab


	drop table #spindtab

	return  -- #Index_Info

GO
----------------  insert the index information for the db into table -----------------------
PRINT 'Inserting Index Information'

EXEC sp_MSforeachtable @command1="EXEC #Index_Info @objname='?'"

GO
------------------------------------- insert tables with no indexes ------------------------------------

INSERT INTO Index_Information(tablename,
				index_name)

select sysobjects.name,	'No_Indexes'

from sysobjects left outer join Index_Information on

sysobjects.name = Index_Information.tablename

where sysobjects.type = 'U' and 

sysobjects.name <> 'Index_Information' and

Index_Information.tablename is null 


------------------------- clear up the mess -----------------------------------------------

DROP PROCEDURE #Index_Info

--------------------------- delete the stats information ---------------------------------

DELETE FROM Index_Information WHERE Index_Name LIKE '_WA_SYS%'

---------------------------- update the Columns_Indexed  --------------------------------
-- this updates only the simple indexes and composite indexes are not updated

PRINT 'Updating Columns_Indexed'

UPDATE Columns_Indexed SET Indexed = 'Y' FROM

Columns_Indexed INNER JOIN Index_Information ON

Columns_Indexed.Table_Name = Index_Information.tablename AND

Columns_Indexed.Column_Name = Index_Information.Index_Keys

---------------------- define a cursor and update the composite index columns ------------
SET NOCOUNT ON

DECLARE Index_Keys_Cursor CURSOR 
		FOR
SELECT tablename,Index_keys FROM Index_Information

WHERE Index_Keys LIKE '%,%'

DECLARE @tablename VARCHAR(128),
	@Index_Keys VARCHAR(2048),  -- to hold the index keys as returned by stored procedure
	@Index_Key  VARCHAR(128)    -- to hold a single key in the composite keys	

OPEN Index_Keys_Cursor

FETCH NEXT FROM Index_Keys_Cursor INTO @tablename, @Index_Keys

WHILE @@FETCH_STATUS = 0
	BEGIN
		WHILE CHARINDEX(',',@Index_Keys) <> 0 -- loop and update key by key
			BEGIN
				SELECT @Index_Key = LEFT(@Index_Keys,(CHARINDEX(',',@Index_Keys) - 1))  -- get the left most key 
				UPDATE Columns_Indexed SET Indexed = 'Y' WHERE Table_Name = @tablename AND Column_Name = @Index_key
				SELECT @Index_Keys = RIGHT(@Index_Keys,(LEN(@Index_Keys) - CHARINDEX(',',@Index_Keys)))
			END

		UPDATE Columns_Indexed SET Indexed = 'Y' WHERE Table_Name = @tablename AND Column_Name = @Index_keys -- only one key left

		FETCH NEXT FROM Index_Keys_Cursor INTO @tablename, @Index_Keys
	END

CLOSE Index_Keys_Cursor

DEALLOCATE Index_Keys_Cursor

PRINT ''

PRINT 'Execution Complete'