How to Identify Non-Active SQL Server Indexes

Load of the Report to a SQL Table

In any database of your choosing, create the following table:

CREATE TABLE [ dbo].[Analysis ] (

[ Table ] [ to varchar ] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[ Indice ] [ to varchar ] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[ Uso ] [ to varchar ] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[ Peso ] [ int ] NOT NULL,

[ Base ] [ to varchar ] (15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,

[ Date ] [ datetime ] NOT NULL

) ON [ PRIMARY ]

Once the table has been created, you can use DTS Import to load the text file into the SQL Table. When importing the text file, choose the TAB as the column delimiter, and the table Analysis as the destination.

Once the text file is imported, you can use the following stored procedure to list the non-active indexes.

CREATE PROCEDURE Indices_Inactivos AS

declare

it @Base varchar(15),

@Tabla varchar(50),

@Indice varchar(100),

@BaseAnt varchar(15),

@TablaAnt varchar(50)

IndicesInactivos declares for cursor

select Base, Table, Indice

from Analysis

where Indice like ' [ [ ]IX% '

group by Base, Table, Indice

having sum(convert(money, replace(Uso,',','.'))) = 0

they open IndicesInactivos

fetch next from IndicesInactivos into @Base, @Tabla, @Indice

while (@@FETCH_STATUS < > -1)

begin

if @Base < > @BaseAnt

begin

Print ' BASES: ' + @Base

select @BaseAnt = @Base

select @TablaAnt = ''

end

if @Tabla < > @TablaAnt

begin

Print ' TABLE: ' + @Tabla

Print ' INDICES: '

select @TablaAnt = @Tabla

end

Print ' ' + @Indice

fetch next from IndicesInactivos into @Base, @Tabla, @Indice

continue

end

close IndicesInactivos

deallocate IndicesInactivos

GO