The indexing schema plays a fundamental role in performance tuning, and tools such as the Index Tuning Wizard that can suggest efficient schemas on the grounds of a database usage. Once the indexing schema has been completed, you should document it in order to know which tables have primary keys, which have clustered keys, which and how many are the non clustered indexes of a certain table. You can get this metadata through SQL Server Information Schema’s views, that are based on the following system tables: sysobjects, syscolumns, sysreferences, spt_values, and sysindexes.
Unfortunately these tables don’t provide all the necessary values, but the Books Online documentation lets to derive the meaning of all the fields in these system tables. The following sp_help_db_indexes stored procedure, placed inside the master database, lets to view all the indexes of each table for the current database, with the respective name, type and fields that make it up:
/* SP sp_help_db_indexes: enumerates all the indexes of all the DB’s tables Author : Giuseppe Dimauro */CREATE procedure sp_help_db_indexesASdeclare @empty varchar(1)select @empty = ”– 35 is the lenght of the name field of the master.dbo.spt_values tabledeclare @IgnoreDuplicateKeys varchar(35), @Unique varchar(35), @IgnoreDuplicateRows varchar(35), @Clustered varchar(35), @Hypotethical varchar(35), @Statistics varchar(35), @PrimaryKey varchar(35), @UniqueKey varchar(35), @AutoCreate varchar(35), @StatsNoRecompute varchar(35)select @IgnoreDuplicateKeys = name from master.dbo.spt_values where type = ‘I’ and number = 1 –ignore duplicate keysselect @Unique = name from master.dbo.spt_values where type = ‘I’ and number = 2 –uniqueselect @IgnoreDuplicateRows = name from master.dbo.spt_values where type = ‘I’ and number = 4 –ignore duplicate rowsselect @Clustered = name from master.dbo.spt_values where type = ‘I’ and number = 16 –clusteredselect @Hypotethical = name from master.dbo.spt_values where type = ‘I’ and number = 32 –hypotethicalselect @Statistics = name from master.dbo.spt_values where type = ‘I’ and number = 64 –statisticsselect @PrimaryKey = name from master.dbo.spt_values where type = ‘I’ and number = 2048 –primary keyselect @UniqueKey = name from master.dbo.spt_values where type = ‘I’ and number = 4096 –unique keyselect @AutoCreate = name from master.dbo.spt_values where type = ‘I’ and number = 8388608 –auto createselect @StatsNoRecompute = name from master.dbo.spt_values where type = ‘I’ and number = 16777216 –stats no recomputeselect o.name, i.name, ‘index description’ = convert(varchar(210), –bits 16 off, 1, 2, 16777216 on case when (i.status & 16)<>0 then @Clustered else ‘non’+@Clustered end + case when (i.status & 1)<>0 then ‘, ‘+@IgnoreDuplicateKeys else @empty end + case when (i.status & 2)<>0 then ‘, ‘+@Unique else @empty end + case when (i.status & 4)<>0 then ‘, ‘+@IgnoreDuplicateRows else @empty end + case when (i.status & 64)<>0 then ‘, ‘+@Statistics else case when (i.status & 32)<>0 then ‘, ‘+@Hypotethical else @empty end end + case when (i.status & 2048)<>0 then ‘, ‘+@PrimaryKey else @empty end + case when (i.status & 4096)<>0 then ‘, ‘+@UniqueKey else @empty end + case when (i.status & 8388608)<>0 then ‘, ‘+@AutoCreate else @empty end + case when (i.status & 16777216)<>0 then ‘, ‘+@StatsNoRecompute else @empty end), ‘index column 1’ = index_col(o.name,indid, 1), ‘index column 2’ = index_col(o.name,indid, 2), ‘index column 3’ = index_col(o.name,indid, 3)from sysindexes i, sysobjects owhere i.id = o.id and indid > 0 and indid < 255 -all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) and o.type = 'U' -user table --ignore the indexes for the autostat and (i.status & 64) = 0 -index with duplicates and (i.status & 8388608) = 0 -auto created index and (i.status & 16777216)= 0 --stats no recompute order by o.name
########################################################
This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli
########################################################
