Technical Article

Show valuable info from sysindexes

,

This sp (sp_sysindexes) captures important columns from the table sysindexes and translates the numeric values into text. It shows the owner.object, the filegroup which it belongs to, the type of index, the total number of pages for tables/clustered indexes and non clustered indexes. It also shows the status, the indid and the type of object.
You can execute it within any database or specify the database.
If you specify an option, the sp can also generate a log file with its result.

example: sp_sysindexes 'northwind', 'Y'

Create proc sp_sysindexes
@db_name Varchar (30) = NULL, --Database name where it executes
@LogFile Char(1) = 'N' --Indicates if it generates a log file or not(Y or N) 
As
/*
** Author: Rodrigo Acosta
** E-mail: acosta_rodrigo@hotmail.com
** Date 08/31/2002
** sp_sysindexes @db_name, @logfile
** It shows clear information from sysindexes
*/
Set Nocount ON

/*
** If a database name was entered it checks that it exists.
** If it does not exist, it shows an error and the available databases. 
*/If @db_name is Not Null
Begin
If Not Exists
(Select name from master.dbo.sysdatabases Where name = @db_name)
Begin
Raiserror ('The database does not exist in the server.', 16, 1)
Print ('Available databases are:')
EXEC sp_databases
Return
End
End

/*
** It checks if the entered option for the log file is Y or N
** If it is not one of them, shows an error and exits.
*/If @Logfile<>'Y' And @LogFile<>'N' 
Begin
Raiserror ('You must enter Y or N to generate the log file.',16,1)
Return
End


/* If no database was entered , it executes the sp in the actual database.*/If @db_name is NULL 
Begin
Set @db_name = (Select db_name())
End


/*
**Variables
*/Declare @Select Varchar (1200) --it saves the select sentence
Declare @groupid tinyint--it saves the filegroup id
Declare @name sysname--it saves the object name
Declare @filegroup_nameVarchar (30)--it saves the filegroup name
Declare @used int--it saves used pages
Declare @uid int--it saves the object owner id

Set @select='select sysindexes.name, 
"Status"=
Case
When sysindexes.Status = 2 Then "Unique index"
When sysindexes.Status = 1 Then "Cancel command if attempt to insert duplicate key"
When sysindexes.Status = 4 Then "Cancel command if attempt to insert duplicate Row"
When sysindexes.Status = 16 Then "Clustered index"
When sysindexes.Status = 64 Then "Index allows duplicate rows"
When sysindexes.Status = 2048 Then "Index used to enforce PRIMARY KEY constraint"
When sysindexes.Status = 4096 Then "Index used to enforce UNIQUE constraint"
Else "No Status"
End
,"Indid"=
Case
When Indid = 1 Then "Clustered Index or Table"
When Indid > 1 And Indid < 255 Then "Nonclustered Index"
When Indid = 255 Then "Tables that have text or image data"
When Indid = 0 Then "Indid equal to 0"
Else "No Indid"
End
,Groupid
,used
,xtype
,uid
From ' + @db_name + '..SysIndexes  sysindexes Inner Join ' + @db_name + '..Sysobjects sysobjects
On sysobjects.id = sysindexes.id 
Order By sysindexes.Name'

/*It creates a temp table ##sysindexes to save the select*/Create Table ##sysindexes
(Name sysname,
Status Varchar(50),
Indid Varchar(36),
groupid Varchar (30),
used Varchar (47),
xtype Char(2),
uid VarChar (40))

/*It inserts the records in the temp table*/Insert ##sysindexes
EXEC (@select)

/*
** It creates a cursor to save the name, groupid, used, uid to update the temp table 
** with the filegroup name and the object owner. 
*/Declare vCursor Cursor For
Select [name], groupid, used ,uid From ##sysindexes Order By [name]

Open vCursor --It opens the cursor.
FETCH NEXT FROM vCursor INTO @name, @groupid, @used, @uid
WHILE @@FETCH_STATUS = 0
BEGIN
/*It changes the groupid into the filegroup name*/Set @filegroup_name=(Select filegroup_name(@groupid)) --It saves the filegroup name.
Update ##sysindexes
Set groupid=@filegroup_name 
Where name=@name

/*It changes the used for tables and clustered indexes*/Update ##sysindexes
Set used = 'Number of data pages used: ' + Convert(Varchar (4), @used)
Where (Indid = 'Clustered Index or Table' Or Indid = 'Indid equal to 0') And [name] = @name

/*It changes the used for nonclustered indexes*/Update ##sysindexes
Set used = 'Number of index pages used: ' + Convert(Varchar (4), @used)
Where Indid = 'Nonclustered Index' And [name] = @name

/*It changes the used for text or image*/Update ##sysindexes
Set used = 'Number of text/image pages used: ' + Convert(Varchar (4), @used)
Where Indid = 'Tables that have text or image data' And [name] = @name

/*It changes the uid to the owner*/Update ##sysindexes
Set uid = (Select user_name(@uid))
Where uid = @uid And name = @name

FETCH NEXT FROM vCursor INTO @name, @groupid, @used, @uid
End

/*Closes the cursor*/Close vCursor
Deallocate vCursor

/*It changes the filegroup NULL to 'no Filegroup'*/Update ##sysindexes
Set groupid='No Filegroup'
Where groupid Is Null

/*It shows the table ##sysindexes*/Select uid + '.' + Name  As 'Object name',
groupid As 'Filegroup',
Status,
Indid,
used As 'Pages Info',
'Object type' =
Case
When xtype = 'U' Then 'Of user'
When xtype = 'S' Then 'Of system'
End
from ##sysindexes
Order by [Name]


/*
** If Y was specified, it generates the log file
*/If @Logfile='Y' 
Begin
EXEC master.dbo.xp_cmdshell 'Osql -E -Q"Select name,groupid,status,indid,used from ##sysindexes" -oc:\Sysindexes.Log'
Print 'A log file was generated in C:\ named Sysindexes.log'
End

/*It deletes the table ###sysindexes*/Drop table ##sysindexes

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating