USE [master]
GO

/****** Object:  StoredProcedure [dbo].[spShrinkAllDatabases]    Script Date: 08/12/2011 15:40:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:             Dan Sutton
-- Create date: 06/14/2011
-- Description:      Reindex all tables and shrink Logs and Data Files in Server
-- =============================================
CREATE PROCEDURE [dbo].[spShrinkAllDatabases]
AS
BEGIN
       SET NOCOUNT ON;
  DECLARE @dbname VARCHAR(50)
  DECLARE @logname VARCHAR(50)
  DECLARE @sql VARCHAR(2000)
  DECLARE @TableName varchar(255)

  DECLARE cDB CURSOR FOR SELECT name FROM sys.databases
  OPEN cDB
  FETCH NEXT FROM cDB INTO @dbname
  WHILE @@FETCH_STATUS=0
    BEGIN
      PRINT ('Shrinking Database '+@dbname+' ...')
      BEGIN TRY
        EXEC ('ALTER DATABASE ['+@dbname+'] SET RECOVERY SIMPLE')
        DECLARE cLog CURSOR FOR SELECT name FROM sys.database_files WHERE type_desc='LOG'
        OPEN cLog
        FETCH NEXT FROM cLog INTO @logname
        WHILE @@FETCH_STATUS=0
          BEGIN
            BEGIN TRY
              DBCC SHRINKFILE (@logname,0)
            END TRY BEGIN CATCH END CATCH
            FETCH NEXT FROM cLog INTO @logname
          END
        CLOSE cLog
        DEALLOCATE cLog
        DBCC SHRINKDATABASE (@dbname, TRUNCATEONLY)
      END TRY
      BEGIN CATCH
        PRINT ('- Shrink Failed')
      END CATCH
      BEGIN TRY
        EXEC ('ALTER DATABASE ['+@dbname+'] SET RECOVERY FULL')
      END TRY BEGIN CATCH END CATCH

      -- Now rebuild all the indexes in the database
      create table #temp_reindex(TableName varchar(255))
      EXEC ('INSERT INTO #temp_reindex'
        +' SELECT ''['+@dbname+'].[''+OBJECT_SCHEMA_NAME([OBJECT_ID],DB_ID('''+@dbname+'''))+''].[''+name+'']'''
        +' FROM ['+@dbname+'].sys.tables')
      DECLARE tb CURSOR LOCAL FOR SELECT TableName from #temp_reindex
      OPEN tb
      FETCH NEXT FROM tb INTO @TableName
      WHILE @@FETCH_STATUS=0
        BEGIN
          PRINT ('Rebuilding Indexes for '+@TableName+' ...')
          BEGIN TRY
            EXEC ('ALTER INDEX ALL ON '+@TableName+' REBUILD WITH (FILLFACTOR=80)')
          END TRY
          BEGIN CATCH
            PRINT ('- Reindex Failed')
          END CATCH
          FETCH NEXT FROM tb INTO @TableName
        END
      CLOSE tb
      DEALLOCATE tb
      DROP TABLE #temp_reindex

      FETCH NEXT FROM cDB INTO @dbname
    END
  CLOSE cDB
  DEALLOCATE cDB
END    

GO
Share|