Labels

Script To Reindex Database In Sql Server


To use this script pass database name as variable to procedure and this will rebuild all indexes having fragmentation greater than 30 %

/***************************************************************************/
Create Procedure RUN_INDEX ( @databse_name varchar (1000))
As
BEGIN
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
Declare @avg_fragmentation_in_percent int

SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name IN (@databse_name )
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
Select @Database
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
select @avg_fragmentation_in_percent = avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (DB_ID(@Database), object_id(@Table), NULL, NULL, 'DETAILED')
if @avg_fragmentation_in_percent > 30
BEGIN
Select 'Reindexing table'+ @Table + 'having fragmentation '
+ CAST( @avg_fragmentation_in_percent as varchar(100) )
select @Table
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD '
EXEC (@cmd)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
END
 /***************************************************************************/
 

No comments:
Write comments

Please do not enter spam links

Services

More Services