Monday, April 23, 2012

SQL Server script to rebuild all indexes for all tables and all databases

DECLARE @Database VARCHAR(255)   DECLARE @Table VARCHAR(255)  DECLARE @cmd NVARCHAR(500)  DECLARE @fillfactor INT

SET
@fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR 
SELECT
name FROM MASTER.dbo.sysdatabases   WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   ORDER BY
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'''  

  
-- create table cursor 
  
EXEC (@cmd
  
OPEN TableCursor  

  
FETCH NEXT FROM TableCursor INTO @Table  
  
WHILE @@FETCH_STATUS = 0  
  
BEGIN  

       IF
(@@MICROSOFTVERSION / POWER(2, 24) >= 9)
      
BEGIN
          
-- SQL 2005 or higher command
          
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
          
EXEC (@cmd)
      
END
       ELSE
       BEGIN
          
-- SQL 2000 command
          
DBCC DBREINDEX(@Table,' ',@fillfactor
      
END

       FETCH
NEXT FROM TableCursor INTO @Table  
  
END  

   CLOSE
TableCursor  
  
DEALLOCATE TableCursor 

  
FETCH NEXT FROM DatabaseCursor INTO @Database  END 
CLOSE
DatabaseCursor   DEALLOCATE DatabaseCursor

Simple script to backup all SQL Server databases

DECLARE @name VARCHAR(50-- database name  DECLARE @path VARCHAR(256-- path for backup files  DECLARE @fileName VARCHAR(256-- filename for backup  DECLARE @fileDate VARCHAR(20-- used for file name
SET @path 'C:\Backup\' 
SELECT @fileDate CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR 
SELECT 
name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb'
OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS 0   BEGIN  
       SET 
@fileName @path @name '_' @fileDate '.BAK' 
       
BACKUP DATABASE @name TO DISK = @fileName 

       
FETCH NEXT FROM db_cursor INTO @name   END  

CLOSE 
db_cursor   DEALLOCATE db_cursor