IF object_id('usp_tabledefragment') is not null
drop proc usp_tabledefragment
go
create proc usp_tabledefragment
@maxfrag decimal =30.0
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
declare @rc int
select @rc=0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
select @rc=@@error
if @rc<>0
begin
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Delete the temporary table
DROP TABLE #fraglist
select @rc=-1
return @rc
end
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
SELECT @rc=0
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
select @rc=@@error
if @rc<>0
begin
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
select @rc=-2
return @rc
end
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist