Thursday, June 13, 2013

List Index details with Included columns and identify duplicate indexes

Viewing all columns in an index in a single row has not always been easy.In addition to the regular columns, there are also the included columns and at times we want to view this info all in one place.Here is the code to view all this information along with a query to identify duplicate indexes.

SET NOCOUNT ON
  DECLARE Duplicate_index_cur CURSOR FOR
select sch.name +'.'+ tbl.name,idx.name,col.name,inc.index_column_id,inc.is_included_column
 from sys.indexes idx inner join sys.index_columns inc on idx.object_id = inc.object_id
           and  idx.index_id = inc.index_id
  join sys.columns col on idx.object_id = col.object_id and inc.column_id = col.column_id
           join sys.tables tbl on tbl.object_id = idx.object_id
           join sys.schemas sch on sch.schema_id = tbl.schema_id
order by sch.name, tbl.name,idx.index_id,inc.index_column_id desc

  DECLARE @table_name varchar(256),
 @indexname varchar(256),
 @column_name varchar(256),
 @index_order int ,
 @included_column  int,
 @inc_@column_name varchar(256),
 @col_list varchar(4000) = '',
 @inc_col_list        varchar(4000) = '',
 @table          varchar(256) ,
 @index        varchar(256)

Declare @Indextable table ( tablename varchar(256),
indexname varchar(256),
columnname varchar(256),
included_columns varchar(256))

  OPEN Duplicate_index_cur

  FETCH NEXT
  FROM  Duplicate_index_cur
    INTO @table_name,@indexname,@column_name,@index_order,@included_column

/*Set the Initial Parameters*/
SET @table = @table_name
SET @index = @indexname

  WHILE @@FETCH_STATUS = 0
    BEGIN
        /*Reinitialize Variables*/
IF (@table <> @table_name or @index <> @indexname)
BEGIN
Insert into @Indextable
Select @table,@index,@col_list,@inc_col_list
SET @table = @table_name
SET @index = @indexname
SET @col_list = ''
SET @inc_col_list = ''
END

IF(@included_column = 0)
select @col_list =   @col_list +','+ @column_name
ELSE
select @inc_col_list =  @inc_col_list +','+ @column_name

 FETCH NEXT
  FROM  Duplicate_index_cur
    INTO @table_name,@indexname,@column_name,@index_order,@included_column

    END

Insert into @Indextable
Select @table,@index,@col_list,@inc_col_list
  CLOSE Duplicate_index_cur
  DEALLOCATE Duplicate_index_cur

 /*
 Run this query to list all indexes,columns and included columns
  Select tablename,
indexname,
substring(columnname,2,len(columnname)) as columnname,
substring(included_columns,2,len(included_columns)) as Included_Columns
  from  @Indextable
  */
/*List Duplicate Indexes*/
  select org.tablename,
org.indexname,
dup.indexname,
substring(org.columnname,2,len(org.columnname)) as columnname,
substring(org.included_columns,2,len(org.included_columns)) as Included_Columns
from @Indextable org , @Indextable dup
  where org.tablename = dup.tablename
and org.indexname > dup.indexname
and org.columnname   = dup.columnname
and org.Included_Columns = dup.Included_Columns

No comments:

Post a Comment