Procédure stockée reindexation

Bonjour,

J'ai trouvé sur le net une procédure stockée pour faire soit un
rebuild soit un reorganize des index d'une db en fonction du taux de
fragmentation.
Malheureusement, lorsque je tente de lancer cette procédure stockée,
j'ai les msg d'erreur ci dessous

Msg 207, Level 16, State 1, Line 7
Invalid column name 'tableid'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'tableid'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'indexid'.

Voilà le code de la procédure

CREATE PROCEDURE ap_RebuildIndexes (@maxfrag float, @maxdensity float,
@databasename varchar(255))
AS
/*
EXEC ap_RebuildIndexes 30.0, 75.0, 'OLAP_Program'
*/
SET NOCOUNT ON;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @indexid int;
DECLARE @currentfrag float;
DECLARE @currentdensity float;
DECLARE @partitionnum varchar(10);
DECLARE @partitioncount bigint;
DECLARE @indextype varchar(18);
DECLARE @command varchar(8000);

-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = '#work_to_do')
DROP TABLE #work_to_do;

CREATE TABLE #work_to_do(
IndexID int not null
, IndexName varchar(255) null
, TableName varchar(255) null
, Tableid int not null
, SchemaName varchar(255) null
, IndexType varchar(18) not null
, PartitionNumber varchar(18) not null
, PartitionCount int null
, CurrentDensity float not null
, CurrentFragmentation float not null
);

INSERT INTO #work_to_do(
IndexID, Tableid, IndexType, PartitionNumber, CurrentDensity,
CurrentFragmentation
)
SELECT
fi.index_id
, fi.object_id
, fi.index_type_desc AS IndexType
, cast(fi.partition_number as varchar(10)) AS PartitionNumber
, fi.avg_page_space_used_in_percent AS CurrentDensity
, fi.avg_fragmentation_in_percent AS CurrentFragmentation
FROM sys.dm_db_index_physical_stats(db_id(@databasename), NULL, NULL,
NULL, 'SAMPLED') AS fi
WHERE (fi.avg_fragmentation_in_percent > @maxfrag
OR fi.avg_page_space_used_in_percent < @maxdensity)
AND page_count> 8
AND fi.index_id > 0

--Assign the index names, schema names, table names and partition
counts
EXEC ('UPDATE #work_to_do SET TableName = t.name, SchemaName = s.name,
IndexName = i.Name
,PartitionCount = (SELECT COUNT(*) pcount
FROM '
+ @databasename + '.sys.Partitions p
where p.Object_id = w.Tableid
AND p.index_id = w.Indexid)
FROM '
+ @databasename + '.sys.tables t INNER JOIN '
+ @databasename + '.sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN #work_to_do w ON t.object_id = w.tableid INNER JOIN '
+ @databasename + '.sys.indexes i ON w.tableid = i.object_id and
w.indexid = i.index_id');

--Declare the cursor for the list of tables, indexes and partitions to
be processed.
--If the index is a clustered index, rebuild all of the nonclustered
indexes for the table.
--If we are rebuilding the clustered indexes for a table, we can
exclude the
--nonclustered and specify ALL instead on the table

DECLARE rebuildindex CURSOR FOR
SELECT CASE WHEN IndexType = 'Clustered Index' THEN 'ALL' ELSE
IndexName END AS IndexName
, TableName
, SchemaName
, IndexType
, PartitionNumber
, PartitionCount
, CurrentDensity
, CurrentFragmentation
FROM #work_to_do i
WHERE NOT EXISTS(
SELECT 1
FROM #work_to_do iw
WHERE iw.TableName = i.TableName
AND iw.IndexType = 'CLUSTERED INDEX'
AND i.IndexType = 'NONCLUSTERED INDEX')
ORDER BY TableName, IndexID;

-- Open the cursor.
OPEN rebuildindex;

-- Loop through the tables, indexes and partitions.
FETCH NEXT
FROM rebuildindex
INTO @indexname, @objectname, @schemaname, @indextype,
@partitionnum, @partitioncount, @currentdensity, @currentfrag;

WHILE @@FETCH_STATUS = 0
BEGIN

-- If the index is more heavily fragmented, issue a REBUILD.
Otherwise, REORGANIZE.
IF @currentfrag < 30
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' +
@databasename + '.' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + @partitionnum;
EXEC (@command);
END;

IF @currentfrag >= 30
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' +
@databasename + '.' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + @partitionnum;
EXEC (@command);
END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM rebuildindex INTO @indexname, @objectname,
@schemaname, @indextype, @partitionnum, @partitioncount,
@currentdensity, @currentfrag;
END;
-- Close and deallocate the cursor.
CLOSE rebuildindex;
DEALLOCATE rebuildindex;

GO


J'ai bien tenté de trouver ce qui n'allait pas, mais là je sèche ...
dommage parceque cette procédure m'avait l'air bien sympathique.
Merci d'avance pour l'aide que vous pourrez m'apporter.

Dimitri

Bonjour,

J'ai trouvé sur le net une procédure stockée pour faire soit un
rebuild soit un reorganize des index d'une db en fonction du taux de
fragmentation.
Malheureusement, lorsque je tente de lancer cette procédure stockée,
j'ai les msg d'erreur ci dessous

Msg 207, Level 16, State 1, Line 7
Invalid column name 'tableid'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'tableid'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'indexid'.

Voilà le code de la procédure

CREATE PROCEDURE ap_RebuildIndexes (@maxfrag float, @maxdensity float,
@databasename varchar(255))
AS
/*
EXEC ap_RebuildIndexes 30.0, 75.0, 'OLAP_Program'
*/
SET NOCOUNT ON;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @indexid int;
DECLARE @currentfrag float;
DECLARE @currentdensity float;
DECLARE @partitionnum varchar(10);
DECLARE @partitioncount bigint;
DECLARE @indextype varchar(18);
DECLARE @command varchar(8000);

-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = '#work_to_do')
DROP TABLE #work_to_do;

CREATE TABLE #work_to_do(
IndexID int not null
, IndexName varchar(255) null
, TableName varchar(255) null
, Tableid int not null
, SchemaName varchar(255) null
, IndexType varchar(18) not null
, PartitionNumber varchar(18) not null
, PartitionCount int null
, CurrentDensity float not null
, CurrentFragmentation float not null
);

INSERT INTO #work_to_do(
IndexID, Tableid, IndexType, PartitionNumber, CurrentDensity,
CurrentFragmentation
)
SELECT
fi.index_id
, fi.object_id
, fi.index_type_desc AS IndexType
, cast(fi.partition_number as varchar(10)) AS PartitionNumber
, fi.avg_page_space_used_in_percent AS CurrentDensity
, fi.avg_fragmentation_in_percent AS CurrentFragmentation
FROM sys.dm_db_index_physical_stats(db_id(@databasename), NULL, NULL,
NULL, 'SAMPLED') AS fi
WHERE (fi.avg_fragmentation_in_percent > @maxfrag
OR fi.avg_page_space_used_in_percent < @maxdensity)
AND page_count> 8
AND fi.index_id > 0

--Assign the index names, schema names, table names and partition
counts
EXEC ('UPDATE #work_to_do SET TableName = t.name, SchemaName = s.name,
IndexName = i.Name
,PartitionCount = (SELECT COUNT(*) pcount
FROM '
+ @databasename + '.sys.Partitions p
where p.Object_id = w.Tableid
AND p.index_id = w.Indexid)
FROM '
+ @databasename + '.sys.tables t INNER JOIN '
+ @databasename + '.sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN #work_to_do w ON t.object_id = w.tableid INNER JOIN '
+ @databasename + '.sys.indexes i ON w.tableid = i.object_id and
w.indexid = i.index_id');

--Declare the cursor for the list of tables, indexes and partitions to
be processed.
--If the index is a clustered index, rebuild all of the nonclustered
indexes for the table.
--If we are rebuilding the clustered indexes for a table, we can
exclude the
--nonclustered and specify ALL instead on the table

DECLARE rebuildindex CURSOR FOR
SELECT CASE WHEN IndexType = 'Clustered Index' THEN 'ALL' ELSE
IndexName END AS IndexName
, TableName
, SchemaName
, IndexType
, PartitionNumber
, PartitionCount
, CurrentDensity
, CurrentFragmentation
FROM #work_to_do i
WHERE NOT EXISTS(
SELECT 1
FROM #work_to_do iw
WHERE iw.TableName = i.TableName
AND iw.IndexType = 'CLUSTERED INDEX'
AND i.IndexType = 'NONCLUSTERED INDEX')
ORDER BY TableName, IndexID;

-- Open the cursor.
OPEN rebuildindex;

-- Loop through the tables, indexes and partitions.
FETCH NEXT
FROM rebuildindex
INTO @indexname, @objectname, @schemaname, @indextype,
@partitionnum, @partitioncount, @currentdensity, @currentfrag;

WHILE @@FETCH_STATUS = 0
BEGIN

-- If the index is more heavily fragmented, issue a REBUILD.
Otherwise, REORGANIZE.
IF @currentfrag < 30
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' +
@databasename + '.' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + @partitionnum;
EXEC (@command);
END;

IF @currentfrag >= 30
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' +
@databasename + '.' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + @partitionnum;
EXEC (@command);
END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM rebuildindex INTO @indexname, @objectname,
@schemaname, @indextype, @partitionnum, @partitioncount,
@currentdensity, @currentfrag;
END;
-- Close and deallocate the cursor.
CLOSE rebuildindex;
DEALLOCATE rebuildindex;

GO


J'ai bien tenté de trouver ce qui n'allait pas, mais là je sèche ...
dommage parceque cette procédure m'avait l'air bien sympathique.
Merci d'avance pour l'aide que vous pourrez m'apporter.

Dimitri

> Je ne trouve pas la fonction similaire "sys.dm_db_index_physical_stats" p our 2000


SELECT
fi.index_id
, fi.object_id
, fi.index_type_desc AS IndexType
, cast(fi.partition_number as varchar(10)) AS PartitionNumber
, fi.avg_page_space_used_in_percent AS CurrentDensity
, fi.avg_fragmentation_in_percent AS CurrentFragmentation

FROM sys.dm_db_index_physical_stats(db_id('Mach1'), NULL, NULL,NULL, 'S AMPLED') AS fi