SQL Server for Statistical database indexing

--1. Creating a temporary table #index_clusteres
IF object_id (' tempdb.. #index_clusteres ') is not a NULL DROP TABLE #index_clusteres
CREATE TABLE #index_clusteres (tablename varchar), index_name varchar ($), index_description varchar, index_ Keys VARCHAR (200))

--2. Production index data cur_clustered (including system process Sp_helpindex)

DECLARE @objname varchar (776)
Declare cur_clustered cursor Local for
SELECT object_name (ID) from dbo.sysindexes
WHERE indid = 1 and object_name (ID) in (' Ant_changenote ', ' bd_accruedinterests ', ' bd_basicinfo ')
ORDER by object_name (ID)

Open cur_clustered--opening cursors
while (1=1)
Begin
FETCH NEXT from cur_clustered to @objname; --Move the cursor to the first piece of data
If @ @fetch_status!=0 break; --for jumping out of loops

--The following from the process: sp_helpindex
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)
@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 is local to the current database.
Select @dbname = ParseName (@objname, 3)
If @dbname is null
Select @dbname = db_name ()
else if @dbname <> db_name ()
Begin
RAISERROR (15250,-1,-1)
End

-Check to see the table exists and initialize @objid.
Select @objid = object_id (@objname)
If @objid is NULL
Begin
RAISERROR (15009,-1,-1, @objname, @dbname)
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 has any indexes.
End

IF object_id (' tempdb.. #spindtab ') is not a NULL DROP TABLE #spindtab
--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
)

--now check out all index, figure out the 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 is.
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 was 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

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)

--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
INSERT INTO #index_clusteres
Select
@objname,
' Index_name ' = index_name,
' Index_description ' = convert (varchar),--bits off, 1, 2, 16777216 on, located on group
case if index_id = 1 Then ' clustered ' else ' nonclustered ' end
+ Case-Ignore_dup_key <>0 then ', ignore duplicate keys ' else ' end
+ Case-Is_unique <>0 then ', unique ' else ' end
+ Case-is_hypothetical <>0 then ', hypothetical ' else ' end
+ Case-Is_primary_key <>0 then ', primary key ' else ' end
+ Case-Is_unique_key <>0 then ', unique key ' else ' end
+ Case-auto_created <>0 then ', auto create ' else ' end
+ Case-No_recompute <>0 then ', stats no recompute ' else ' end
+ ' located on ' + groupname),
' Index_keys ' = Index_keys
From #spindtab
ORDER BY Index_name

End
Close Cur_clustered--closing cursors
Deallocate cur_clustered--Release cursor

SQL Server for Statistical database indexing