/*************************************************************************************

 To implement this, do a Find and Replace

 Replace every occurrence of [Keyhouse] with [correctdatabasenamehere]
 
 *************************************************************************************/

USE [Keyhouse]

SET NOCOUNT ON

IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.objects [SO]
    WHERE [SO].[name] = 'kh_reindexviews'
      AND [SO].[type] = 'U')
BEGIN
  EXEC ('DROP TABLE [dbo].[kh_reindexviews]')
END
GO

CREATE TABLE
       [dbo].[kh_reindexviews]
      ([id]         INT              IDENTITY(1, 1)       NOT NULL,
       [name]       NVARCHAR(128)                         NOT NULL,
       [viewname]   NVARCHAR(128)                         NOT NULL,
       CONSTRAINT [PK_kh_reindexviews]
                  PRIMARY KEY CLUSTERED
                 ([id])
               ON [PRIMARY])
    ON [PRIMARY]
GO

INSERT
  INTO [dbo].[kh_reindexviews]
      ([name],
       [viewname])
  SELECT [SI].[name],
         [SO].[name]
    FROM sys.objects [SO] --Include indexes on Schema Bound Views (hence 'U', 'V' below) but NOT on Fulltext Indexes.
         INNER JOIN SYS.indexes [SI]
                    LEFT OUTER JOIN sys.fulltext_indexes [FI]
                                 ON [FI].[object_id] = [SI].[object_id]
                                AND [FI].[unique_index_id] = [SI].[index_id]
                 ON [SI].[object_id] = [SO].[object_id]
                AND [SI].[name] IS NOT NULL
   WHERE [SO].[type] IN ('U', 'V')
     AND [SO].[name] <> 'kh_reindexviews'
     AND [FI].[object_id] IS NULL --don't include the NOT NULL ones that are used for Fulltext Indexes
ORDER BY [SO].[name],
         [SI].[name]

DECLARE @ID INT
DECLARE @View_Name NVARCHAR(128)
DECLARE @Index_Name NVARCHAR(128)
DECLARE @FileSizeMB DECIMAL(20, 2)
DECLARE @MaxFileSizeMB DECIMAL(20, 2)
DECLARE @LogFileName NVARCHAR(128)
DECLARE @DBName NVARCHAR(128)
DECLARE @NCOMMAND NVARCHAR(MAX)

SET @DBName = '[Keyhouse]'
SET @DBName = REPLACE(@DBName, '[', '')
SET @DBName = REPLACE(@DBName, ']', '')

SELECT @LogFileName = CONVERT(NVARCHAR(128), [mf].[name])
  FROM sys.databases [db]
       INNER JOIN sys.master_files [mf]
               ON [mf].[database_id] = [db].[database_id]
              AND [mf].[type_desc] = 'LOG'
 WHERE [db].[name] = @DBName

--SELECT * FROM [dbo].[kh_reindexviews]

SET @MaxFileSizeMB = 0

EXEC ('ALTER DATABASE [Keyhouse] SET RECOVERY SIMPLE WITH NO_WAIT')
SET @NCOMMAND = N'DBCC SHRINKFILE ([' + @LogFileName + N'], 1, TRUNCATEONLY) WITH NO_INFOMSGS'
EXEC sp_executesql @NCOMMAND
EXEC ('ALTER DATABASE [Keyhouse] SET RECOVERY FULL WITH NO_WAIT')

WHILE EXISTS
     (SELECT TOP 1 1
        FROM [dbo].[kh_reindexviews])
  BEGIN
    BEGIN TRANSACTION

    SET @ID = NULL
      SET @View_Name = NULL
      SET @Index_Name = NULL

    SELECT TOP 1
           @ID = [id],
           @View_Name = [viewname],
           @Index_Name = [name]
      FROM [dbo].[kh_reindexviews]

    --SET @NCOMMAND = N'ALTER INDEX [' + @Index_Name + N'] ON [dbo].[' + @View_Name + N'] REBUILD'
    SET @NCOMMAND = N'DBCC DBREINDEX ([' + @View_Name + N'], [' + @Index_Name + N'], 90) WITH NO_INFOMSGS'
    --SELECT @NCOMMAND

    EXEC sp_executesql @NCOMMAND

    DELETE [dbo].[kh_reindexviews] WHERE [id] = @ID

    COMMIT

    SELECT @FileSizeMB = CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128))
      FROM sys.databases [db]
           INNER JOIN sys.master_files [mf]
                   ON [mf].[database_id] = [db].[database_id]
                  AND [mf].[type_desc] = 'LOG'
     WHERE [db].[name] = @DBName

    IF @FileSizeMB > @MaxFileSizeMB
      BEGIN
        SET @MaxFileSizeMB = @FileSizeMB
      END

    EXEC ('ALTER DATABASE [Keyhouse] SET RECOVERY SIMPLE WITH NO_WAIT')
    SET @NCOMMAND = N'DBCC SHRINKFILE ([' + @LogFileName + N'], 1, TRUNCATEONLY) WITH NO_INFOMSGS'
    EXEC sp_executesql @NCOMMAND
    EXEC ('ALTER DATABASE [Keyhouse] SET RECOVERY FULL WITH NO_WAIT')

  END

  SELECT @MaxFileSizeMB AS [MaximumLogFileDuringRebuild]
GO

USE [Keyhouse]
exec sys.sp_updatestats
GO

USE [Keyhouse]
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.objects [SO]
    WHERE [SO].[name] = 'kh_reindexviews'
      AND [SO].[type] = 'U')
BEGIN
  EXEC ('DROP TABLE [dbo].[kh_reindexviews]')
END
GO

USE [Keyhouse]
GO
DECLARE @LogFileName NVARCHAR(128)
DECLARE @DBName NVARCHAR(128)
DECLARE @NCOMMAND NVARCHAR(MAX)

SET @DBName = '[Keyhouse]'
SET @DBName = REPLACE(@DBName, '[', '')
SET @DBName = REPLACE(@DBName, ']', '')

SELECT @LogFileName = CONVERT(NVARCHAR(128), [mf].[name])
  FROM sys.databases [db]
       INNER JOIN sys.master_files [mf]
               ON [mf].[database_id] = [db].[database_id]
              AND [mf].[type_desc] = 'LOG'
 WHERE [db].[name] = @DBName

ALTER DATABASE [Keyhouse] SET RECOVERY SIMPLE WITH NO_WAIT
SET @NCOMMAND = N'DBCC SHRINKFILE ([' + @LogFileName + N'], 1, TRUNCATEONLY) WITH NO_INFOMSGS'
EXEC sp_executesql @NCOMMAND
ALTER DATABASE [Keyhouse] SET RECOVERY FULL WITH NO_WAIT
GO

SET NOCOUNT OFF
GO