Hi This Script will not give any output but will rebuild or reorganize indexes.
This Script is only for production environment and it will create a job, You have to run the job. For other environments Please run below listed codes(a,b).
It will update the statistics of these indexes. 🙂
a.
/* Query developed by Vimal Lohani For reorganizing and rebuilding indexes */
/* Query work for all Online Databases in one go */
/* Work for index name available other wise will say INDEX NAME NOT SET OR NULL */
/* Work for user define indexes */
/* Still work if any error occored with indexes */
USE MASTER
GO
SET NOCOUNT ON
If exists (select * from tempdb.sys.all_objects where name like ''#Databases'')
begindrop table #Databases end
SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Banco
INTO #Databases
FROM sys.databases
WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') --Not including system databases
and name NOT LIKE ''ReportServer%''
AND compatibility_level > 80 --For Database sql 2005 or higher
AND state=0 --fOR All online databases
ORDER BY Banco;
DECLARE
@Loop INT = 1,
@QT INT = (SELECT COUNT(1) FROM #Databases), --max no of databases
@Banco VARCHAR(50);
WHILE @Loop <= @QT
BEGIN
SET @Banco = (SELECT Banco FROM #Databases WHERE Seq = @Loop);
EXEC(
''USE '' + @Banco + ''; '' +
''PRINT ''''For Database : '''' + db_name();
Declare @dbid int;
set @dbid=DB_ID();
SELECT
ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,
t.name Tabela, h.name Esquema,
i.name Indice, p.avg_fragmentation_in_percent Frag
INTO #Consulta
FROM
sys.dm_db_index_physical_stats(@dbid,null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
join sys.objects o on (o.object_id=p.object_id)
where p.avg_fragmentation_in_percent > 5.0
and p.index_id >= 0 --all indexes including heap
and p.page_count >= 10
AND o.type=''''U''''
ORDER BY Esquema, Tabela;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #Consulta),
@Comando VARCHAR(500),
@Updato VARCHAR(500)
WHILE @Loop <= @Total
BEGIN
SELECT @Comando = ''''ALTER INDEX '''' + Indice +
'''' ON '''' + Esquema + ''''.'''' + Tabela +
( CASE WHEN Frag > 45.0 THEN '''' REBUILD'''' ELSE '''' REORGANIZE'''' END)
FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Comando);
PRINT ''''Executed: '''' + ISNULL(@Comando,''''INDEX NAME NOT SET OR NULL'''');
SELECT @Updato=''''UPDATE STATISTICS ''''+ Esquema + ''''.'''' +Tabela + '''' ''''+Indice FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Updato);
PRINT ''''UPDATE STATISTICS: ''''+ ISNULL(@Updato,''''INDEX NAME NOT SET OR NULL'''');
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '''' Total No of indexes defragmented : '''' + CONVERT(VARCHAR(5),@Total);
PRINT ''''------------------------------------------------------------------------------'''';
DROP TABLE #Consulta;'');
SET @Loop = @Loop + 1;
END;
DROP TABLE #Databases;
b.
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN (''model'', ''tempdb'')
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ''USE ['' + @DB +'']'' + CHAR(13) + ''EXEC sp_updatestats'' + CHAR(13)
PRINT @SQL
exec(@sql)
Select @SQL= ''USE ['' + @DB +'']'' + CHAR(13) + ''alter database ['' + @DB +'']'' +'' set multi_user''+ CHAR(13)
PRINT @SQL
exec(@SQL)
print ''------------------------------------------------''
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
CLOSE curDB
DEALLOCATE curDB
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature