/* Microsoft SQL Server - Scripting			*/
/* Server: FSNBSNA				*/
/* Database: master					*/
/* Creation Date 2/11/99 12:52:39 PM 			*/

/****** Object:  Stored Procedure dbo.sp_check_all    Script Date: 2/11/99 12:52:41 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_check_all') and sysstat & 0xf = 4)
	drop procedure dbo.sp_check_all
GO

CREATE PROCEDURE sp_check_all
AS

/*
---------------------------------------------------------------
-- SP_CHECK_ALL BUILD 30
-- SP_CHECK_ALL is designed to daily maintain your databases and
-- tables.  It will update statistics, check consistence, and fix
-- allocations.  WARNING : THIS SCRIPT WILL PUT YOUR USER DEFINED
-- DATABASES IN SINGLE USER MODE.  IF YOU DO NOT WANT THIS, TAKE
-- OUT THE PIECE OF THE PROCEDURE WHERE NOTED.
-- Procedure will give you reverse generate reports first for
-- disaster recovery.  Other than that run at your own risk.
-- 

-- Date      Purpose
-- 2/1/99    Created Brian Knight knight_brian@hotmail.com
-- 2/10/99   Reverse Generate Databases 
-- 2/11/99   Rebuild Indexes on available UDD - Brian Knight 
-- 4/8/99    Certified for SQL Server 7.0
---------------------------------------------------------------
*/
	/* PRINT DEVICE INFORMATION */
	PRINT "Beginning Sp_Check_all Build 29"
	PRINT ""
	PRINT "Device Information"
	PRINT ""
	EXEC ("sp_HELPDEVICE")
	PRINT ""
	PRINT "Configuration Information"
	PRINT ""
	EXEC ("sp_configure")
	/* PRINT SYSINDEXES */
	PRINT ""
	PRINT "Indexes"
	PRINT ""
	EXEC ("Select * from sysINDEXES")
	/* PRINT SYSDEVICES */
	PRINT ""
	PRINT "Sys Devices"
	PRINT ""
	EXEC ("Select * from sysDEVICES")

DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE @dataname varchar(30)
DECLARE @singleuser varchar(75)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
        WHERE name not in ('master', 'pubs', 'tempdb', 'model')

OPEN datanames_cursor

  FETCH NEXT FROM datanames_cursor INTO @dataname

  WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
        BEGIN

          FETCH NEXT FROM datanames_cursor INTO @dataname
          CONTINUE
        END

         
      SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
      PRINT " "

      PRINT @dataname_header
      PRINT " "

      EXEC ("USE " + @dataname + " DECLARE tnames_cursor CURSOR FOR SELECT
	name from sysobjects where type = 'U'")
	
	
	Select @dataname_header = RTRIM(UPPER(@dataname))
	Select @singleuser = "SP_DBOPTION " + RTRIM(UPPER(@dataname)) + ", 'single user'"
/*
---------------------------------------------------------------
-- TAKE THIS PIECE OUT IF YOU DO NOT WANT YOUR DATABASE TO GO TO 
-- SINGLE USER MODE 
---------------------------------------------------------------
*/

	PRINT ""
	PRINT "***************----------------*****************"
	PRINT "Taking Database Into Single User Mode"
	PRINT "***************----------------*****************"
	PRINT ""

	EXEC (@singleuser +  ", true")
	PRINT "Single User Mode Active for database"

	/*PRINT " ***************----------------*****************"
	PRINT "Fixing Allocations"

	PRINT " ***************----------------*****************"
	PRINT ""
	*/
	/*EXEC ("DBCC FIX_AL " + "(" + @dataname + ")")*/

	PRINT " ***************----------------*****************"
	PRINT "CHECKING DATABASE"
	PRINT " "

	EXEC ("DBCC CHECKDB " + "(" + @dataname + ")")

	PRINT ""
	PRINT " ***************----------------*****************"
	PRINT "CHECKING CATALOG"
	PRINT " ***************----------------*****************"
	PRINT ""

	EXEC ("DBCC CHECKCATALOG " + "(" + @dataname + ")")

	PRINT " ***************----------------*****************"
	PRINT "CHECKING ALLOCATIONS"
	PRINT " ***************----------------*****************"
	PRINT ""

	EXEC ("DBCC CHECKALLOC " + "(" + @dataname + ")")
	/*
---------------------------------------------------------------
-- TAKE THIS PIECE OUT IF YOU DO NOT WANT YOUR DATABASE TO GO TO 
-- SINGLE USER MODE 
---------------------------------------------------------------
*/

	PRINT " ***************----------------*****************"

	PRINT "Taking Database Out Of Single User Mode"
	PRINT " ***************----------------*****************"
	EXEC (@singleuser +  ", false")
   	OPEN tnames_cursor

      FETCH NEXT FROM tnames_cursor INTO @tablename

      WHILE (@@fetch_status <> -1)
        BEGIN
          IF (@@fetch_status = -2)            BEGIN
              FETCH NEXT FROM tnames_cursor INTO @tablename
              CONTINUE
            END
    
               SELECT @tablename_header = "  Updating " +
RTRIM(UPPER(@tablename))

	

	PRINT ""
          PRINT @tablename_header

	/* Update Statistics */

          EXEC ("UPDATE STATISTICS " + @dataname + ".." + @tablename )
	
	FETCH NEXT FROM tnames_cursor INTO @tablename
        END

      DEALLOCATE tnames_cursor
      FETCH NEXT FROM datanames_cursor INTO @dataname
    END

DEALLOCATE datanames_cursor

PRINT " "

PRINT " "
PRINT "Maintenance is now complete for all user-defined tables."
GO