Quick script to defragment SQL Server indexes

Quick script to defragment SQL Server indexes

Here is a quick script that will defragment the indexes in your database and updates the statistics. If the indexes are fragmented in more than one database then you should run this script on each one of your databases. Beware, don’t run this script while your database is in use because rebuilding your indexes will impact the response times of queries.

The script will run on SQL Server 2005 and later versions:

set nocount on
DECLARE @sql_string NVARCHAR(max)
DECLARE @table_Name sysname
DECLARE @Schema_Name sysname
DECLARE Table_Cur CURSOR FOR
SELECT t.Name, s.Name
FROM sys.tables t inner join sys.schemas s on t.Schema_id = s.Schema_Id
OPEN Table_Cur
FETCH NEXT FROM Table_Cur
INTO @table_Name, @Schema_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_string = 'ALTER INDEX ALL ON [' + @Schema_Name + '].[' + @table_Name + '] REBUILD'
PRINT @sql_string
begin try
EXEC sp_ExecuteSQL @sql_string
end try
begin catch
select
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
end catch
SET @sql_string = 'UPDATE STATISTICS [' + @Schema_Name + '].[' + @table_Name + ']'
PRINT @sql_string
begin try
EXEC sp_ExecuteSQL @sql_string
end try
begin catch
select
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
end catch
FETCH NEXT FROM Table_Cur
INTO @table_Name, @Schema_Name
END
CLOSE Table_Cur
DEALLOCATE Table_Cur

Be careful not to defragment SQL Server indexes during normal usage. It is best to schedule this outside your regular business hours or during periods of low database use. Note that this will only defragment indexes in the database where the script is run. So if you have multiple databases with fragmented indexes it should be run in each one.

We can tune your SQL Server for a fixed price. Please visit our SQL Server Performance Tuning page.