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