SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
DROP PROCEDURE dbo.NISA_Maintenance
GO
CREATE PROCEDURE dbo.NISA_Maintenance

--Name: sp_system_admin_maintenance
--Purpose: This stored Procedure(SP) will be used for running nightly maintenance on
-- all databases. It resides in the master database of each database server.
-- 1) DBCC DBREINDEX on all tables.
-- 2) UPDATE STATISTICS on all tables.
-- 3) Recompile all procedures.
-- 4) UPDATE USAGE on the database.
-- 5) DBCC CHECKDB on the database.
-- 
--Format: EXEC sp_system_admin_maintenance
--
--Example: EXEC sp_system_admin_maintenance
--
--Action: Author: Date: Comments:
--------- ------------- --/--/---- ----------------------------------------
--Created Derrick Leggett 11/11/2003 Initial Development
--Modified Derrick Leggett 02/17/2004 Added shrinkdatabase logic into maintenance.
--

-- EXEC dbo.NISA_Maintenance

AS

SET ARITHABORT ON

--Record start time
PRINT 'START TIME: ' + CAST(GETDATE() AS VARCHAR(255))

--Declare needed variables.
DECLARE
	@int_error_counter INT,
	@int_max	INT,
	@int_counter	INT,
	@txt_sql	NVARCHAR(4000),
	@txt_name	NVARCHAR(4000),
	@txt_owner	NVARCHAR(255),
	@txt_db		NVARCHAR(255),
	@t1		DATETIME

--Declare table variable to hold table or procedure names.
DECLARE	@tbl_names TABLE (
	int_id		INT IDENTITY(1,1) PRIMARY KEY,
	txt_name	VARCHAR(255),
	txt_owner	VARCHAR(255))

--Insert into table all user tables.
INSERT	@tbl_names (txt_name,txt_owner)
	SELECT	so.name, su.name
		FROM	sysobjects so
		INNER JOIN sysusers su ON so.uid = su.uid
		WHERE	so.xtype = 'U'
		AND	so.name NOT LIKE 'dt%'

--Set up loop to run DBCC commands against all user tables.
SELECT	@int_max = (SELECT MAX(int_id) FROM @tbl_names),
	@int_counter = (SELECT MIN(int_id) FROM @tbl_names)

WHILE @int_counter <= @int_max BEGIN
	SELECT @txt_name = (SELECT '[' + txt_owner + '].[' + txt_name + ']'
				FROM @tbl_names
				WHERE int_id = @int_counter)

	--Reindex all user tables.
	SELECT @txt_sql = 'DBCC DBREINDEX(''' + @txt_name + ''')'
	
	PRINT @txt_sql
	SET	@T1 = GETDATE()
	EXEC ( @txt_sql )
	PRINT CAST(DATEDIFF(ms, @T1, GETDATE()) AS VARCHAR)
	
	SELECT @int_counter = @int_counter + 1
END

--Insert into table all procedures.
DELETE @tbl_names

INSERT @tbl_names(txt_name, txt_owner)

	SELECT	so.name, su.name
		FROM	sysobjects so
		INNER JOIN sysusers su
			ON so.uid = su.uid
		WHERE	so.xtype = 'P'

--Set up loop to force a recompile of all stored procedures.
SELECT	@int_max = (SELECT MAX(int_id) FROM @tbl_names),
	@int_counter = (SELECT MIN(int_id) FROM @tbl_names)

WHILE @int_counter <= @int_max BEGIN

	SELECT	@txt_name = (SELECT '[' + txt_owner + '].[' + txt_name + ']'
		FROM @tbl_names
		WHERE int_id = @int_counter)
	
	--Recompile the procedures.
	SELECT @txt_sql = 'EXEC sp_recompile ''' + @txt_name + ''''
	
	PRINT @txt_sql
	EXEC ( @txt_sql )
	
	SELECT @int_counter = @int_counter + 1
END

SELECT @txt_db = DB_NAME()

--Update the usage on the database.
PRINT 'DBCC UPDATEUSAGE (' + @txt_db + ') WITH NO_INFOMSGS'
DBCC UPDATEUSAGE (@txt_db) WITH NO_INFOMSGS

--Update the usage on the database.
PRINT 'DBCC CHECKDB (' + @txt_db + ') WITH NO_INFOMSGS'
DBCC CHECKDB (@txt_db) WITH NO_INFOMSGS

--Record start time of checkpoint..
PRINT 'CHECKPOINT START TIME: ' + CAST(GETDATE() AS VARCHAR(255))

PRINT 'CHECKPOINT'
CHECKPOINT

--Record end time of checkpoint..
PRINT 'CHECKPOINT END TIME: ' + CAST(GETDATE() AS VARCHAR(255))

--Record end time.
PRINT 'END TIME: ' + CAST(GETDATE() AS VARCHAR(255))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO