Skip to main content

Programmatically rebuilding all index of a database or for all databases (TSQL Code/Script)


Generally we use Maintenance Plan to rebuild all indexes of all tables of a database. However if you want to accomplish similar task programmatically, below script/s (TSQL) will be beneficial.

Please replace the value of @fillfactor and Database Name (highlighted in yellow) with the required values from your script.  

Script to rebuild all indexes of a database:

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT
SET @fillfactor = 90  -- Change this fillfactor as per your requirement
DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name = '[Database Name]'
ORDER BY 1 
OPEN DatabaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 
   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'''  
   EXEC (@cmd) 
   OPEN TableCursor  
   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
       EXEC (@cmd) 
       print 'All index rebuilt...on table:'
       print @Table
       FETCH NEXT FROM TableCursor INTO @Table  
   END  
   CLOSE TableCursor  
   DEALLOCATE TableCursor 
   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor


Script to rebuild all indexes of all database:

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT
SET @fillfactor = 90  -- Change this fillfactor as per your requirement
DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN  '[tempdb]' – Here we are excluding tempdb which means all other database will be reindexed.
ORDER BY 1 
OPEN DatabaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 
   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'''  
   EXEC (@cmd) 
   OPEN TableCursor  
   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
       EXEC (@cmd) 
       print 'All index rebuilt...on table:'
       print @Table
       FETCH NEXT FROM TableCursor INTO @Table  
   END  
   CLOSE TableCursor  
   DEALLOCATE TableCursor 
   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor 
Labels:
Location: Greer, SC 29650, USA

Comments

Follower said…
really helpful posting
May 20, 2010 at 8:13 PM
Post a Comment

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release
Post a Comment
Read more

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [
Post a Comment
Read more

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch 
Post a Comment
Read more