/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	Script Name:	Table MaintenanceII.SQL

	Location:	\\IMBC_EXC_01\SQL Scripts\Maintenance\

	Author:		Mickael R. Escroignard
					Resolution Systems Ltd
					44 (0)181 665 5050
					Copyright 1998

	Cration Date:	30/04/98

	Modified on:	09/05/98

	Purpose:	Run the following commands for all Databases on current Server:
					(except master, model, msdb, tempdb, pubs)

				- DBCC CHECKCATALOG		|-> DB Level
				- DBCC NEWALLOC			|-> DB level
				- DBCC CHECKTABLE		|-> Table Level
				- UPDATE STATISTICS		|-> Table Level
				- sp_recompile			|-> Table Level
			
	Note:		* This script uses 2 cursors and 1 Temp Table, 1 cursor to scroll
			through the databases in master..sysdatabases to fetch DB Name,
			then scrolls though all the tables within this database,
			then fetches the next DB Name and carries on.
			* Due to the fact that sp_recompile cannot be used in this cursor,
			I had to pipe the USE + DB Name to a file... then add GO to it...
			then add all the table names with a GO in between, then execute run
			isql with the generated file name (C:\RECOMP.SQL) outside of the loop.
			This file gets deleted at that end of the script.
			* The result of this statement is logged into a file called
			C:\Result.txt.
			At the end of the script, this file is emailed to Sharon E. Eaton and 
			Mickael R. Escroignard via their MS Exchange accounts.
			* This script is better used as a stored procedure and added to the
			SQL Executive list of tasks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

SET NOCOUNT ON

DECLARE 	@database  	char(30),
		@DBName		char(30),
		@table		char(30),
		@sql		char(255),
		@command	char(255),
		@command2 	char(255),
		@fetch_outer	integer,
		@fetch_inner	integer

DECLARE 	@msg 		char(255)

/* Initialize variables */
SELECT 		@database = null
SELECT		@DBName = null
SELECT 		@table = null
SELECT 		@sql = null
SELECT 		@command = null
SELECT 		@fetch_outer = -1
SELECT 		@fetch_inner = -1

/* Create temp table */
PRINT 'Creating #temp table... Please wait...'
CREATE TABLE #tables
(name	char(30)   not null)

/* Declaring outer cursor */
PRINT 'Declaring database cursor... Please wait...'
DECLARE cur_db cursor FOR
	SELECT name FROM master..sysdatabases
	WHERE name NOT IN ('master','model','tempdb','msdb', 'pubs')
	FOR READ ONLY

/* Open outer cursor and perform initial fetch */
PRINT 'Opening database cursor and performing initial fetch...Please wait...'
OPEN cur_db

FETCH cur_db INTO @database

/* Save @@fetch_status */
SELECT @fetch_outer = @@fetch_status

/* Loop through all databases */
WHILE (@fetch_outer = 0)

	BEGIN
	
	/*************************************************************************************/
	/*                             DBCC CHECKCATALOG statement                           */
	/*************************************************************************************/

	SELECT @sql = "DBCC CHECKCATALOG (" + rtrim(ltrim(@database)) + ")"

	/* Execute DBCC CHECKCATALOG */
	SELECT 'Performing DBCC CHECKCATALOG on: ' + rtrim(ltrim(@database))
	SELECT @command = "master..xp_cmdshell " + "'isql -Usa -P -Q"+'"' + rtrim(ltrim(@sql)) + '"'+"'"
	EXEC (@command)



	/*************************************************************************************/
	/*                               DBCC NEWALLOC statement                             */
	/*************************************************************************************/

	SELECT @sql = 'DBCC NEWALLOC (' + rtrim(ltrim(@database)) + ')'

	/* Execute DBCC NEWALLOC */
	SELECT 'Performing DBCC NEWALLOC on: ' + rtrim(ltrim(@database))
	SELECT @command = "master..xp_cmdshell " + "'isql -Usa -P -Q"+'"' + rtrim(ltrim(@sql)) + '"'+"'"
	EXEC (@command)


	/* Fill up temp table */
	PRINT 'Building temp table with list of tables...Please wait...'
	SELECT @command = 'insert into #tables select name from ' + rtrim(ltrim(@database)) + "..sysobjects where type = 'U' ORDER BY name"
	EXEC (@command)
	SELECT name FROM #tables

	/* Declare, open, and perform initial fetch of inner cursor */
	PRINT 'Declare, open, and perform and initial fetch on tables cursor...Please wait...'
	DECLARE cur_tbl CURSOR 
		FOR
			SELECT name FROM #tables
	FOR READ ONLY
	
	open cur_tbl
	FETCH cur_tbl INTO @table

	/* Save @@fetch_status */
	SELECT @fetch_inner = @@fetch_status

	/* Loop through all tables */

	/* Initialise Recompile SQL Script */
	
	SELECT @command= "'Echo Use "+ rtrim(ltrim(@database)) + " > C:\RECOMP.SQL" + "'"
	SELECT @command2 = "master..xp_cmdshell "  + @command
	EXEC (@command2)


	SELECT @command= "'Echo GO >> C:\RECOMP.SQL'"
	SELECT @command2 = "master..xp_cmdshell " + @command
	EXEC (@command2)

	WHILE (@fetch_inner = 0)
		BEGIN

			/*********************************************************************/
			/*                      DBCC CHECKTABLE statement                    */
			/*********************************************************************/

			SELECT @sql = 'DBCC CHECKTABLE (''''' + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + ''''')'

			/* Execute DBCC CHECKTABLE */
			SELECT 'Performing DBCC CHECKTABLE table on: ' + ltrim(rtrim(@table))
			SELECT @command = "master..xp_cmdshell " + "'isql -Usa -P -Q"+'"' + rtrim(ltrim(@sql)) + '"'+"'"
			EXEC (@command)


			/*********************************************************************/
			/*                      UPDATE STATISTICS statement                  */
			/*********************************************************************/

			SELECT @sql = 'UPDATE STATISTICS ' + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table))

			/* Execute UPDATE STATISTICS */
			SELECT 'Performing UPDATE STATISTICS table on: ' + ltrim(rtrim(@table))
			SELECT @command = "master..xp_cmdshell " + "'isql -Usa -P -Q"+'"' + rtrim(ltrim(@sql)) + '"'+"'"
			EXEC (@command)



			/*********************************************************************/
			/*                        sp_recompile statement                     */
			/*********************************************************************/
			
			SELECT @sql = "sp_recompile '" + rtrim(ltrim(@database)) + ".." + rtrim(ltrim(@table)) + "'"
			
			/* Generate recompile Script*/
			SELECT 'Generating sp_recompile script adding table: ' + ltrim(rtrim(@table))


			SELECT @command= '"Echo '+  rtrim(ltrim(@sql)) + ' >> C:\RECOMP.SQL' + '"'
			SELECT @command2 = "master..xp_cmdshell " + @command
			EXEC (@command2)

			SELECT @command= "'Echo GO >> C:\RECOMP.SQL'"
			SELECT @command2 = "master..xp_cmdshell " + @command
			EXEC (@command2)

			/* Fetch next table */
			PRINT 'Fetching next table...Please wait...'
			FETCH NEXT FROM cur_tbl INTO @table
			SELECT @fetch_inner = @@fetch_status
		END

	/* Close and deallocate inner cursor */
	PRINT 'Closing and deallocating table cursor...Please wait...'
	CLOSE cur_tbl
	DEALLOCATE cur_tbl

	/* Execute Index recompile script */
	SELECT @msg='Executing Table Recomplie Script C:\RECOMP.SQL For Database: '+@database
	PRINT @msg
	SELECT @command = "master..xp_cmdshell 'isql -Usa -P -iC:\RECOMP.SQL'"
	EXEC (@command)

	/* Truncate the Temp Table */
	SELECT @sql = 'truncate table #tables'
	EXEC (@sql)

	/* Fetch next database */
	PRINT 'Fetching next database...Please wait...'
	FETCH NEXT FROM cur_db INTO @database
	SELECT @fetch_outer = @@fetch_status
	END

/* Close and deallocate outer cursor */
PRINT 'Closing and deallocating database cursor...Please wait...'
CLOSE cur_db
DEALLOCATE cur_db

/* Drop Temporary Table */
PRINT 'Droping temp table...Please wait...'
DROP table #tables

/* Delete Temporary File: C:\RECOMP.SQL */
PRINT 'Deleting Temporary File: C:\RECOMP.SQL...Please wait...'
SELECT @command = "master..xp_cmdshell 'del C:\RECOMP.SQL'"
EXEC (@command)

/* Now send the email */
PRINT 'Now sending an email to Mickael and Sharon with Copy of the result file...Please wait...'
xp_sendmail
	@recipients = "Mickael Escroignard;Sharon Eaton",
	@message = "Here is a copy of the file generated after sp_TableMaint was run last saturday. This email was sent to Sharon E. Eaton and Mickael R. Escroignard. Have a nice day :) Bye!",
	@attachments = "C:\result.txt",
	@subject = "Message from the Administrator at MFB...>"

SET NOCOUNT OFF
GO