DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to REBUILD all indexes in database with FILLFACTOR?

Execute the following Microsoft SQL Server T-SQL database administration script in SSMS Query Editor to REBUILD all indexes in the AdventureWorks2008 sample database.

-- SQL Server 2008 script to REBUILD all indexes for all tables

USE AdventureWorks2008;

DECLARE @DatabaseName SYSNAME   = DB_NAME(),  @TableName VARCHAR(256) 

DECLARE @FILLFACTOR INT = 85

DECLARE @DynamicSQL NVARCHAR(max) =

 'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +

 ''.'' + TABLE_NAME AS TABLENAME  

 FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE

 TABLE_TYPE = ''BASE TABLE'''  

 

BEGIN 

  EXEC sp_executeSQL @DynamicSQL  -- create tables cursor

  OPEN curAllTablesInDB  

  FETCH NEXT FROM curAllTablesInDB INTO @TableName  

  WHILE (@@FETCH_STATUS = 0) 

  BEGIN  

       SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +

         ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')' 

       PRINT @DynamicSQL

       -- ALTER INDEX ALL ON Purchasing.ShipMethod REBUILD WITH (FILLFACTOR = 85)

       EXEC sp_executeSQL @DynamicSQL 

       FETCH NEXT FROM curAllTablesInDB INTO @TableName  

   END   -- cursor WHILE

   CLOSE curAllTablesInDB  

   DEALLOCATE curAllTablesInDB 

END

------------

-- INDEX REBUILD ONLINE

USE AdventureWorks2008;

GO

CREATE PROC sprocDBIndexREBUILD @FILLFACTOR INT = 90

AS

BEGIN

DECLARE @DatabaseName SYSNAME   = DB_NAME(),  @TableName VARCHAR(256) 

DECLARE @DynamicSQL NVARCHAR(max) =

 'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +

 ''.'' + TABLE_NAME AS TABLENAME  

 FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE

 TABLE_TYPE = ''BASE TABLE'''  

BEGIN 

  EXEC sp_executeSQL @DynamicSQL  -- create tables cursor

  OPEN curAllTablesInDB  

  FETCH NEXT FROM curAllTablesInDB INTO @TableName  

  WHILE (@@FETCH_STATUS = 0) 

  BEGIN  

       SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +

         ' REBUILD WITH (ONLINE=ON, FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')' 

       -- PRINT @DynamicSQL

       EXEC sp_executeSQL @DynamicSQL 

       FETCH NEXT FROM curAllTablesInDB INTO @TableName  

   END   -- cursor WHILE

   CLOSE curAllTablesInDB  

   DEALLOCATE curAllTablesInDB 

END

END -- sproc

GO

------------

-- Index fragmentation report

SELECT SCHEMA_NAME(o.schema_id)     AS SchemaName,

       OBJECT_NAME(a.object_id)     AS TableName,

       i.name                       AS IndexName,

       a.index_id                   AS IndexID,

       convert(tinyint,a.avg_fragmentation_in_percent) AS [Fragment%]

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL,

       NULL, 'LIMITED') AS a

       INNER JOIN sys.indexes i

         ON i.index_id = a.index_id

         AND i.object_id = a.object_id 

       INNER JOIN sys.objects o

         ON a.object_id = o.object_id

ORDER BY SchemaName, TableName, IndexID

------------

Related articles:

Ola Hallengren: SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

SQL SERVER ReIndexing Database Tables and Update Statistics on Tables

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

http://www.sqlusa.com/bestpractices2008/performancetuning/missingindexes/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE