if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DB_CheckCatalog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DB_CheckCatalog]
GO

CREATE PROCEDURE sp_DB_CheckCatalog @Database sysname

/* Procedure will run DBCC CHECKCATALOG for the database provided. 0 - success, 1- fail */

AS

DECLARE 	@sqlstring nvarchar(250),
		@rtn int


SET	@sqlstring = 'DBCC CHECKCATALOG (' + LTRIM(RTRIM(@Database)) + ')'

EXEC	@rtn = sp_executesql  @sqlstring

IF @@Error <>0 OR @rtn <>0
BEGIN
	SET 	@sqlstring = 'DBCC CHECKCATALOG FOR DATABASE ' + LTRIM(RTRIM(@Database)) 

	EXEC	sp_Activity_Log "FAIL", @sqlstring

	RETURN 1
END

SET 	@sqlstring = 'DBCC CHECKCATALOG FOR DATABASE ' + LTRIM(RTRIM(@Database)) 

EXEC	dba..sp_Activity_Log "COMPLETE", @sqlstring

RETURN 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DB_Reindex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DB_Reindex]
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_DB_Reindex @Database sysname

/* Procedure will run DBCC DBREINDEX for each table in the specified database, 0 - success, 1 - fail */

AS
DECLARE 	@tablename sysname,
		@tableid int,
		@sqlstring nvarchar(250),
		@rtn int

SET	@sqlstring = 'DECLARE CUR CURSOR FOR SELECT Name, id FROM ' + @Database + '..sysobjects WHERE xtype = ' + CHAR(39) + 'U' + CHAR(39) 

EXEC (@sqlstring)

OPEN CUR
	FETCH NEXT FROM Cur INTO @tablename, @tableid
	WHILE @@fetch_status =0
	BEGIN

		SET	@sqlstring = 'select Name AS IndexName from ' + @Database + '..sysindexes where id = ' + CAST(@tableid AS CHAR(10)) + ' and [first] <>0 and status <>0'
		
		EXEC (@sqlstring)
		IF @@rowcount>0
		BEGIN

			SET	@sqlstring = 'USE ' + LTRIM(RTRIM(@Database)) + ' DBCC DBREINDEX (' + LTRIM(RTRIM(@tablename)) + ')'
			EXEC	@rtn = sp_executesql @sqlstring

			IF @@Error <>0 OR @rtn <>0
			BEGIN
				SET 	@sqlstring = 'INDEX UPDATE FOR DATABASE ' + LTRIM(RTRIM(@Database)) + ' TABLE ' + LTRIM(RTRIM(@tablename))
				EXEC	sp_Activity_Log "FAIL", @sqlstring

				GOTO ExitWithErrors
			END

			SET 	@sqlstring = 'INDEX UPDATE FOR DATABASE ' + LTRIM(RTRIM(@Database)) + ' TABLE ' + LTRIM(RTRIM(@tablename))
			EXEC	dba..sp_Activity_Log "COMPLETE", @sqlstring
		END

	FETCH NEXT FROM Cur INTO @tablename,  @tableid
	END

	CLOSE Cur
	DEALLOCATE Cur
	RETURN 0

ExitWithErrors:

	CLOSE Cur
	DEALLOCATE Cur
	RETURN 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Backup_Data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Backup_Data]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE sp_Backup_Data
AS
DECLARE @DBID int,
	@Database sysname,
	@rtn int,
	@sqlstring nvarchar(250),
	@Param_Value_1 char(10),
	@backup_device_path 	varchar(250)


/************************************************************************************/
/** IMPORTANT! Change backup device path to the one you use!!! **********************/
/************************************************************************************/

SET	@backup_device_path = "C:\MSSQL7\BACKUP\"

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



SELECT	@DBID = 0


TRUNCATE TABLE tbl_Curr_DB_Status

WHILE	(1=1)
BEGIN

	SET ROWCOUNT 1

	SELECT 	@DBID = DBID, @Database = NAME 
	FROM 
		master..sysdatabases 
	WHERE 
		Name NOT IN ('tempdb')
	AND	DBID > @DBID
	ORDER BY DBID

	IF @@rowcount = 0
	BEGIN
		SET ROWCOUNT 0	
		BREAK
	END

	SET ROWCOUNT 0	



	/*********** CHECK IF BACKUP DEVICE EXISTS FOR THE DATABASE *****************/

	IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE name = 'L_' + @Database)
	BEGIN
		SET 	@sqlstring = ' USE master EXEC master..sp_addumpdevice ' + CHAR(39) + 'disk' + CHAR(39) + ', ' + CHAR(39) + 
				'L_' + @Database + CHAR(39) + ', ' + CHAR(39) + @backup_device_path + 'L_'+ @Database + '.BAK' + CHAR(39)

		EXEC	@rtn = sp_executesql @sqlstring

		IF @@error <> 0 OR @rtn = 1
		BEGIN
			SET 	@sqlstring = 'FAIL TO CREATE BACKUP DEVICE. No more task will be executed for database '+@Database
			EXEC	sp_Activity_Log "FAIL", @sqlstring
			CONTINUE
		END

	END

	/********** CHECK IF DATABASE IN A READ-ONLY MODE ****************************/

	EXEC sp_Get_DB_Options @Database,'read only', @Param_Value_1 OUTPUT
	IF @Param_Value_1 = 'ON'
	BEGIN
		SET 	@sqlstring = 'Database '+@Database+ ' is in READ-ONLY mode'
		EXEC	sp_Activity_Log "SKIP", @sqlstring

		EXEC    sp_Ins_Curr_DB_Status @Database, 'COMPLETE'

		CONTINUE
	END


	/********** RUN DBCC CHECKDB  ****************************/

	EXEC @rtn = dba..sp_DB_CheckDB @Database
		
	IF @@error <> 0 OR @rtn = 1
	BEGIN
		SET 	@sqlstring = 'No more task will be executed for database '+@Database
		EXEC	sp_Activity_Log "FAIL", @sqlstring
		CONTINUE
	END


	/********** RUN DBCC CHECKCATALOG  ****************************/

	EXEC @rtn = dba..sp_DB_CheckCatalog @Database
		
	IF @@error <> 0 OR @rtn = 1
	BEGIN
		SET 	@sqlstring = 'No more task will be executed for database '+@Database
		EXEC	sp_Activity_Log "FAIL", @sqlstring
		CONTINUE
	END


	/********** REBUILD DATABASE INDEXES ****************************/

	IF @Database NOT IN ('master')
	BEGIN
		EXEC @rtn = dba..sp_DB_Reindex @Database
		
		IF @@error <> 0 OR @rtn = 1
		BEGIN
			SET 	@sqlstring = 'No more task will be executed for database '+@Database
			EXEC	sp_Activity_Log "FAIL", @sqlstring
			CONTINUE
		END
	END

	/********** UPDATE DATABASE STATISTICS ****************************/

	EXEC @rtn = dba..sp_DB_Update_Stats @Database
		
	IF @@error <> 0 OR @rtn = 1
	BEGIN
		SET 	@sqlstring = 'No more task will be executed for database '+@Database
		EXEC	sp_Activity_Log "FAIL", @sqlstring
		CONTINUE
	END


	/********** CREATE FULL DATABASE BACKUP OVERWRITING OLD ONE ****************************/

	EXEC @rtn = dba..sp_DB_Backup_Data @Database
		
	IF @@error <> 0 OR @rtn = 1
	BEGIN
		SET 	@sqlstring = 'No more task will be executed for database '+@Database
		EXEC	sp_Activity_Log "FAIL", @sqlstring
		CONTINUE
	END


END

RETURN 0
GO