/*
15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009

Confidential property of Sybase, Inc.
Copyright 1987, 2009
Sybase, Inc.  All rights reserved.
Unpublished rights reserved under U.S. copyright laws.

This software contains confidential and trade secret information of Sybase,
Inc.   Use,  duplication or disclosure of the software and documentation by
the  U.S.  Government  is  subject  to  restrictions set forth in a license
agreement  between  the  Government  and  Sybase,  Inc.  or  other  written
agreement  specifying  the  Government's rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
*/

declare @retval int
exec @retval = sp_version 'installdbccalt', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'start'
if (@retval != 0) select syb_quit()
go

declare @script_versnum int
select @script_versnum = 15500
if (@@version_number < @script_versnum)
begin
	print "'installdbccalt' is being run on an older ASE installation. ASE version '%1!', install scripts version '%2!'.",
		@@version_number, @script_versnum
	select syb_quit()
end
go

declare @do_quit int
select @do_quit = 0
if (check_db_upgrade('master') = 0)
begin
	print "'installdbccalt' cannot continue, because 'master' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('tempdb') = 0)
begin
	print "'installdbccalt' cannot continue, because 'tempdb' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybsystemprocs') = 0)
begin
	print "'installdbccalt' cannot continue, because 'sybsystemprocs' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('dbccdb') = 0)
begin
	print "'installdbccalt' cannot continue, because 'dbccdb' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('dbccalt') = 0)
begin
	print "'installdbccalt' cannot continue, because 'dbccalt' database is not upgraded to correct version."
	select @do_quit = 1
end
if (@do_quit = 1)
	select syb_quit()
go

use master
go
set flushmessage on
go
if not exists (select * from master..sysdatabases  
                where name = 'dbccalt')  
begin  
	print 'The dbccalt database does not exist.'
	select syb_quit()
end
go
set flushmessage off
go
set nocount on
set proc_return_status off
go

exec sp_dboption dbccalt, 'select into', true 
go
use dbccalt
go
exec sp_drop_object 'sp_validatedb', 'procedure'
go

exec sp_drop_object 'sp_getopid', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_recommendations', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_alterws', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

exec sp_drop_object 'sp_dbcc_run_createws', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_configreport', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_deletehistory', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_deletedb', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_evaluatedb', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

exec sp_drop_object 'sp_dbcc_run_updateconfig', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_summaryreport', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_runcheck', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_statisticsreport', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_faultreport', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

exec sp_drop_object 'sp_dbcc_run_differentialreport', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_fullreport', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_patch_finishtime', 'procedure'
go

exec sp_drop_object 'sp_dbcc_run_exclusions', 'procedure'
go


/*
** This file has the T-SQL scripts to create the tables in
** dbcc database (dbccdb and dbccalt), create appropriate indexes
** from these tables and inilializes dbcc_types  table.
**
** There are 10 tables in dbccdb for storing the configuration and
** results. These tables are :
**	dbcc_types, dbcc_config, dbcc_operation_log, dbcc_operation_results
**	dbcc_counters, dbcc_faults, dbcc_fault_params, dbcc_dev_info,
**	dbcc_exclusions, and dbcc_reco.
**
** Some of these tables have dependencies with other tables. 
** Their dependencies are as follows:
**
** 	1. Tables dbcc_operation_results, dbcc_counters, dbcc_faults and
** 	  dbcc_fault_params depend on dbcc_operation_log. 
** 	2. Tables dbcc_fault_params and dbcc_faults are mutually dependent.
**	3. Table dbcc_exclusions depends on the dbcc_types table.
**	4. Table dbcc_reco depends on the dbcc_types table. 
**
** If a table is missing from dbccdb, all tables that depend on this
** table must be dropped and recreated. If a table already exists,
** it will be dropped and recreated only if any table it depends on
** does not exist. (The exception being dbcc_exclusions, as we 
** retain it even though we automatically drop and recreate dbcc_types, 
** on which dbcc_exclusions depends.)
**
**  We always drop and recreate the dbcc_types and dbcc_reco tables.
**
** All tables have an index. The name of the index is <table_name>_index
** This script drops and recreates indexes of all tables in dbccdb.
** 
** Finally there is a control table dbcc_control. We always drop
** and recreate this. This table is used to store any internal control 
** information to start with this is where the db version will be stored.
** At install time if we need to alter the schema of an existing table, 
** i.e. we are not going to drop and recreate it, we use the dbversion to 
** determine if such alterations are applicable.
*/

/*
** Drop all indexes from dbcc tables.
** These will be created at the end after all upgrade steps have been
** applied.
*/
if (exists (select * from sysindexes 
	where name = 'dbcc_fault_params_index' and indid > 0))
begin
	drop index dbcc_fault_params.dbcc_fault_params_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_faults_index' and indid > 0))
begin
	drop index dbcc_faults.dbcc_faults_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_counters_index' and indid > 0))
begin
	drop index dbcc_counters.dbcc_counters_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_dev_info_index' and indid > 0))
begin
	drop index dbcc_dev_info.dbcc_dev_info_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_operation_results_index' and indid > 0))
begin
	drop index dbcc_operation_results.dbcc_operation_results_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_operation_log_index' and indid > 0))
begin
	drop index dbcc_operation_log.dbcc_operation_log_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_config_index' and indid > 0))
begin
	drop index dbcc_config.dbcc_config_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_types_index' and indid > 0))
begin
	drop index dbcc_types.dbcc_types_index
end
if (exists (select * from sysindexes 
	where name = 'dbcc_exclusions_index' and indid > 0))
begin
	drop index dbcc_exclusions.dbcc_exclusions_index
end
if (exists (select * from sysindexes
	where name = 'dbcc_reco_index' and indid > 0))
begin
	drop index dbcc_reco.dbcc_reco_index
end
go

/*
** create a table to store all table names and populate it 
** with the names of tables we want to create
*/
if (object_id('tempdb..dbcc_table_names') is not null) 
begin
	drop table tempdb..dbcc_table_names
end
go

create table tempdb..dbcc_table_names(name varchar(30))
go

/*
** create a table to store the version of current dbccdb
*/
if (object_id('tempdb..dbcc_dbversion') is not null) 
begin
	drop table tempdb..dbcc_dbversion
end
go

create table tempdb..dbcc_dbversion(dbversion int)
go

/* 
** Check if there is a table named dbcc_control in dbcc database.
** If the table does not exist assume version is 0.
** else select the version from dbcc_control and store it in 
** tempdb..dbcc_dbversion.
*/

declare @dbversion int

if exists  (select * from sysobjects where 
		name = 'dbcc_control' and type = 'U')
begin
	exec ("insert into tempdb..dbcc_dbversion 
		select intvalue from dbcc_control 
		 where keyname = 'DBVERSION'")
	select @dbversion = dbversion from tempdb..dbcc_dbversion
end
if (@dbversion is null)
begin
	select @dbversion = 0
	insert into tempdb..dbcc_dbversion values(@dbversion)
end
go

/*
** Some other tables depend on dbcc_operation_log and dbcc_faults.
** So they are inserted first
*/
insert into tempdb..dbcc_table_names(name) values('dbcc_operation_log')
insert into tempdb..dbcc_table_names(name) values('dbcc_faults')
insert into tempdb..dbcc_table_names(name) values('dbcc_config')
insert into tempdb..dbcc_table_names(name) values('dbcc_types')
insert into tempdb..dbcc_table_names(name) values('dbcc_counters')
insert into tempdb..dbcc_table_names(name) values('dbcc_operation_results')
insert into tempdb..dbcc_table_names(name) values('dbcc_dev_info')
insert into tempdb..dbcc_table_names(name) values('dbcc_fault_params')
insert into tempdb..dbcc_table_names(name) values('dbcc_exclusions')
insert into tempdb..dbcc_table_names(name) values('dbcc_reco')
insert into tempdb..dbcc_table_names(name) values('dbcc_control')
go

/*
** If TRUE, delete the name of tables already
** existing in this database from  tempdb..dbcc_table_names.
*/

/* drop dbcc_control table if it exists */
if exists  (select * from sysobjects where 
		name = 'dbcc_control' and type = 'U')
begin
	drop table  dbcc_control
end

/* drop dbcc_reco table if it exists */
if exists  (select * from sysobjects where
		name = 'dbcc_reco' and type = 'U')
begin
	drop table dbcc_reco
end

/* drop dbcc_types table if it exists */
if exists  (select * from sysobjects where 
		name = 'dbcc_types' and type = 'U')
begin
	drop table  dbcc_types
	insert into tempdb..dbcc_table_names(name) values('dbcc_types')
end

if exists (select * from sysobjects where 
		name = 'dbcc_operation_log' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names 
		where name = 'dbcc_operation_log'
end
else
begin
	/*
	** drop all dependent tables
	*/
	if exists  (select * from sysobjects where 
		name = 'dbcc_operation_results' and type = 'U')
	begin
		drop table dbcc_operation_results 
		insert into tempdb..dbcc_table_names(name) 
				values('dbcc_operation_results')
	end
	if exists  (select * from sysobjects where 
			name = 'dbcc_counter' and type = 'U')
	begin
		drop table dbcc_counter 
		insert into tempdb..dbcc_table_names(name) 
				values('dbcc_counters')
	end
	if exists  (select * from sysobjects where 
			name = 'dbcc_faults' and type = 'U')
	begin
		drop table dbcc_faults 
		insert into tempdb..dbcc_table_names(name) 
			values('dbcc_faults')
	end
	if exists  (select * from sysobjects where 
			name = 'dbcc_fault_params' and type = 'U')
	begin
		drop table dbcc_fault_params
		insert into tempdb..dbcc_table_names(name) 
			values('dbcc_fault_params')
	end
end
if exists  (select * from sysobjects where 
			name = 'dbcc_faults' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names 
		where name = 'dbcc_faults'
end
else
begin
	if exists  (select * from sysobjects where 
			name = 'dbcc_fault_params' and type = 'U')
	begin
		drop table dbcc_fault_params
		insert into tempdb..dbcc_table_names(name) 
			values('dbcc_fault_params')
	end
end
if exists  (select * from sysobjects where 
			name = 'dbcc_fault_params' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names 
		where name = 'dbcc_fault_params'
end
else
begin
	if exists  (select * from sysobjects where 
			name = 'dbcc_faults' and type = 'U')
	begin
		drop table dbcc_faults 
		insert into tempdb..dbcc_table_names(name) 
			values('dbcc_faults')
	end
end
if exists  (select * from sysobjects where 
			name = 'dbcc_config' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names 
		where name = 'dbcc_config'
end
if exists  (select * from sysobjects where 
			name = 'dbcc_dev_info' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names where name = 'dbcc_dev_info'
end
if exists  (select * from sysobjects where 
		name = 'dbcc_operation_results' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names 
		where name = 'dbcc_operation_results'
end
if exists  (select * from sysobjects where 
			name = 'dbcc_counters' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names where name = 'dbcc_counters'
end
if exists  (select * from sysobjects where 
			name = 'dbcc_exclusions' and type = 'U')
begin
	delete  from tempdb..dbcc_table_names 
		where name = 'dbcc_exclusions'
end
go

/*
** Now the table 'tempdb..dbcc_table_names' must have the list of tables
** missing from this database.  Create these tables
** We create dummy tables for each entry in  'tempdb..dbcc_table_names'
** and later rename them. This is because if the table exists at the
** execution time, T-SQL will complain  "There is already an object 
** named '<table name>' in the database" even if create statement is
** within the "if exists" block.
**
** NOTE: The tables are created in their version 0 format and later upgraded
** to the latest version schema. In order to change the schema of any table
** use the UPGRADE sections later in this script instead of changing them in
** place below.
*/
if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_types')
begin
	print "Creating dbcc_types table"
	create table dbcc_types_dummy (type_code  int,
		type_name varchar(255), description varchar(255))
	exec sp_rename  "dbcc_types_dummy", "dbcc_types"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_config')
begin
	print "Creating dbcc_config table"
	create table dbcc_config_dummy (dbid  smallint, type_code  int, 
		value int null, stringvalue varchar(255) null)
	exec sp_rename  "dbcc_config_dummy", "dbcc_config"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_dev_info')
begin
	print "Creating dbcc_dev_info table"
	create table dbcc_dev_info_dummy (name varchar(30), devid  int,
		classid  smallint, random2k real, serial16k real)
	exec sp_rename  "dbcc_dev_info_dummy", "dbcc_dev_info"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_operation_log')
begin
	print "Creating dbcc_operation_log table"
	create table dbcc_operation_log_dummy (dbid  smallint, 
		opid  smallint, optype smallint, suid   int, 
		start  datetime, finish datetime null) 
	exec sp_rename  "dbcc_operation_log_dummy", 
				"dbcc_operation_log"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_operation_results')
begin
	print "Creating dbcc_operation_results table"
	create table dbcc_operation_results_dummy (dbid  smallint, 
		opid  smallint, optype smallint, type_code  int, 
		intvalue  int null, realvalue  real null, 
		binaryvalue varbinary(255) null, 
		stringvalue varchar(255) null, 
		datevalue datetime null)
	exec sp_rename  "dbcc_operation_results_dummy", 
				"dbcc_operation_results"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_counters')
begin
	print "Creating dbcc_counters table"
	create table dbcc_counters_dummy (dbid  smallint, id  int, 
		indid  smallint, partitionid  smallint,  
		devid int, opid  smallint, type_code  int, 
		value  real null)
	exec sp_rename  "dbcc_counters_dummy", "dbcc_counters"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_faults')
begin
	print "Creating dbcc_faults table"
	create table dbcc_faults_dummy (dbid  smallint, id  int, 
		indid  smallint, partitionid  smallint,
		devid int, opid  smallint, faultid  int, 
		type_code  int, status int) lock datapages 
	exec sp_rename  "dbcc_faults_dummy", "dbcc_faults"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_fault_params')
begin
	print "Creating dbcc_fault_params table"
	create table dbcc_fault_params_dummy (dbid  smallint, 
		opid  smallint, faultid  int, type_code  int, 
		intvalue  int null, realvalue real null, 
		binaryvalue varbinary(255) null, 
		stringvalue varchar(255) null, 
		datevalue datetime null)
	exec sp_rename  "dbcc_fault_params_dummy", "dbcc_fault_params"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_exclusions')
begin
	print "Creating dbcc_exclusions table"
	create table dbcc_exclusions_dummy (
		dbid 	smallint not null,
		type	tinyint	not null check (type between 1 and 3) ,
		fault_type	int null,
		table_name	varchar(30) null 
	)

	print "Inserting default exclusions into the dbcc_exclusions table"
	/* exclude space bits mismatch for all databases */
	insert into dbcc_exclusions_dummy values (0, 1, 100035, NULL)

	exec sp_rename  "dbcc_exclusions_dummy", "dbcc_exclusions"
end


if exists  (select * from tempdb..dbcc_table_names
		where name = 'dbcc_reco')
begin
	print "Creating dbcc_reco table"
	create table dbcc_reco_dummy (fault_type int, reco_type int)
	exec sp_rename  "dbcc_reco_dummy", "dbcc_reco"
end

if exists  (select * from tempdb..dbcc_table_names 
		where name = 'dbcc_control')
begin
	print "Creating dbcc_control table"
	create table dbcc_control_dummy (keyname  varchar(30),
		intvalue int null, strvalue varchar(255) null)
	exec sp_rename  "dbcc_control_dummy", "dbcc_control"
end
go

/*
** drop the temporary table. We don't need it any more
*/
if (object_id('tempdb..dbcc_table_names') is not null) 
begin
	drop table tempdb..dbcc_table_names
end
go

/*
** Fill in the dbcc_types table with appropriate type codes
** and populate the dbcc_reco table with fault/recommendation
** mappings.
*/

print "Inserting type codes into dbcc_types table"

/*
** insert type values in dbcc_types table
*/

insert into dbcc_types values 
(1, "max worker processes", "The maximum number of worker processes that may be employed. This is also the maximum level of concurrent processing used. Optional with minimum value 1.")

insert into dbcc_types values 
(2, "dbcc named cache", "The size, in Kbytes, of the buffer cache that will be allocated for the function, and the name for that cache.")

	insert into dbcc_types values 
(3, "scan workspace", "The id and name of the workspace table to be used by the database scan.")


insert into dbcc_types values 
(4, "text workspace", "The id and name of the workspace table that will be used to scan text columns.")


insert into dbcc_types values 
(5, "operation sequence number", "A number identifying the last dbcc operation initiated.")


insert into dbcc_types values 
(6, "database name", "The name of the database from sysdatabases.")


insert into dbcc_types values 
(7, "OAM count threshold", "The percentage by which the OAM counts must disagree to be considered an error.")


insert into dbcc_types values 
(8, "IO error abort", "The IO error threshold for aborting the operation")



insert into dbcc_types values 
(9, "linkage error abort", "The threshold for aborting a object linkage check.")
insert into dbcc_types values 
(10, "enable automatic workspace expansion", "The flag that enables or disables automatic expansion of workspaces when estimated size exceeds the actual workspace size.")

insert into dbcc_types values
(11, "enable dbcc_counter inserts", "The flag that enables or disables inserts in the dbcc_counters table.")

insert into dbcc_types values
(12, "enable excluded faults inserts", "The flag that enables or disables inserts of excluded faults.")

insert into dbcc_types values 
(1000, "hard fault count", "Total count of hard faults.")


insert into dbcc_types values 
(1001, "soft fault count", "Total count of soft faults.")


insert into dbcc_types values 
(1002, "checks aborted count", "Total number of linkage checks aborted.")


insert into dbcc_types values 
(1003, "repairs count", "Total number of repairs effected.")


insert into dbcc_types values 
(1004, "Independent IO count", "Number maximum number of devices that have been found to function independently.")


insert into dbcc_types values 
(1005, "Independent random IO rate", "Estimated throughput for 2K random IO using indepen dently functioning devices.")


insert into dbcc_types values 
(1006, "Independent serial IO rate", "Estimated throughput for 16K serial IO using indepen dently functioning devices.")


insert into dbcc_types values 
(1007, "Text column count", "Total number of non-null text/image column values found")
/* End of Configuration Parameter Types */


/* Beginning of Statistics Types */
insert into dbcc_types values 
(5000, "bytes data", "The size of the relational or index data.")


insert into dbcc_types values 
(5001, "bytes used", "The size of storage used to record the data. Overhead is the difference between `bytes used' and `bytes data'.")


insert into dbcc_types values 
(5002, "pages used", "The number of pages used to hold the object.")


insert into dbcc_types values 
(5003, "pages reserved", "The number of pages allocated to the object.")


insert into dbcc_types values 
(5004, "pages overhead", "The number of pages used for ancillary functions - OAM, distribution pages, control pages, ...")


insert into dbcc_types values 
(5005, "extents used", "The number of extents which hold a portion of the object allocation.")


insert into dbcc_types values 
(5006, "count", "The number of component items in the object - rows, keys ...")


insert into dbcc_types values 
(5007, "max count", "The maximum number of component items on any page.")


insert into dbcc_types values 
(5008, "max size", "The maximum size of any component item on any page.")


insert into dbcc_types values 
(5009, "max level", "The number of levels in the index.")


insert into dbcc_types values 
(5010, "pages misallocated", "The number of pages that appear to be allocated inappro priately to the wrong object.")


insert into dbcc_types values 
(5011, "io errors", "The number of hardware (or platform) errors encountered while accesing pages of this object.")


insert into dbcc_types values 
(5012, "page format errors", "The number of pages that fail intra-page consistency checks.")


insert into dbcc_types values 
(5013, "pages not allocated", "The number of pages of the object that do not appear to be allocated to the object.")


insert into dbcc_types values 
(5014, "pages not referenced", "The number of pages allocated but not referenced.")


insert into dbcc_types values 
(5015, "overflow pages", "The number of overflow data pages.") 


insert into dbcc_types values 
(5016, "page gaps", "The number of pages not linked to the next in sequence.")


insert into dbcc_types values 
(5017, "page extent crosses", "The number of pages linked outside their extent.") 


insert into dbcc_types values 
(5018, "page extent gaps", "The number of extent crosses not to next extent in ascending sequence.")


insert into dbcc_types values 
(5019, "ws buffer crosses", "The number of pages linked outside their workspace buffer.")

insert into dbcc_types values 
(5020, "deleted rows", "The number of deleted rows in the object.")

insert into dbcc_types values 
(5021, "forwarded rows", "The number of forwarded rows in the object.")

insert into dbcc_types values 
(5022, "empty pages", "The number of pages allocated but not containing data.")

insert into dbcc_types values 
(5023, "pages with garbage", "The number of pages that could benefit from garbage collection.")

insert into dbcc_types values 
(5024, "non-contiguous free space", "The number of bytes of non-contiguous free space.")

/* End of Statistics Types */

/* Beginning of Fault Parameter Types */
insert into dbcc_types values 
(10000, "page id", "The id (number) of the page where the fault is located.")

insert into dbcc_types values 
(10001, "page header", "The 32 byte page header for the page where the fault is located.")


insert into dbcc_types values 
(10002, "text column id", "The 8 byte location (page:row:column) of the text value where the fault is located.")


insert into dbcc_types values 
(10003, "object id", "The object or index which contains the fault. The value is an int (id), and a 9 byte varbinary (id:partitionid:indid). The partitionid is zero if the object is not partitioned.")


insert into dbcc_types values 
(10004, "repair action", "A description of the repair performed for the fault.")


insert into dbcc_types values 
(10005, "verification time", "The time when the soft fault was verified.")


insert into dbcc_types values 
(10006, "repair time", "The time when the fault was repaired.")

insert into dbcc_types values 
(10007, "page id expected", "The expected id(number) of the linked page.")

insert into dbcc_types values 
(10008, "page id actual", "The actual id(number) of the linked page.")

insert into dbcc_types values 
(10009, "object id expected", "The expected object id(table and index).")

insert into dbcc_types values 
(10010, "Data Only Locked data page header", "The 44 byte page header for the page where the fault is located.")

insert into dbcc_types values 
(10011, "Data Only Locked btree leaf page header", "The 44 byte page header for the page where the fault is located.")

insert into dbcc_types values 
(10012, "Data Only Locked btree page header", "The 44 byte page header for the page where the fault is located.")

/* End of Fault Parameter Types */

/* Beginning of Object Level Fault Recommendations */
/* Recommendations common to both user tables and system catalogs */
insert into dbcc_types values
(20001, "rerun checkstorage reco", "Rerun checkstorage.")

insert into dbcc_types values
(20002, "indexalloc reco", "Run dbcc indexalloc with the 'fix' option.")

insert into dbcc_types values
(20003, "tablealloc reco", "Run dbcc tablealloc with the 'fix' option.")

insert into dbcc_types values
(20004, "checktable fix_spacebits reco", "Run dbcc checktable with the 'fix_spacebits' option.")

insert into dbcc_types values
(20005, "checktable reco", "Run dbcc checktable.")

insert into dbcc_types values
(20006, "reorg reco", "Run the reorg command.")

insert into dbcc_types values
(20007, "no action reco", "This fault is harmless, no action is required.")

insert into dbcc_types values
(20008, "bulk copy drop object reco", "Bulk copy the data out, drop the object and recreate it, bulk copy the data back in.")

insert into dbcc_types values
(20009, "textalloc/indexalloc/tablealloc reco", "Run any of dbcc textalloc/indexalloc/tablealloc as appropriate in 'optimized' mode and with the 'fix' option.")
   
/* Recommendations unsuitable for system catalogs */
insert into dbcc_types values
(30000, "drop object reco", "Drop the object and recreate it.")

insert into dbcc_types values
(30001, "bulk copy reco", "Bulk copy the data out and back in.")


/* End of Object Level Fault Recommendations */

/* Beginning of Database Level Fault Recommendations */
insert into dbcc_types values
(40000, "check logs for hardware failure reco", "Check your operating system logs and correct all reported hardware problems on disks containing a Sybase device.")

insert into dbcc_types values
(40001, "checkalloc reco", "Run dbcc checkalloc with the 'fix' option.")

insert into dbcc_types values
(40002, "reload db reco", "Reload the database from a clean backup.")

/* End of Database Level Fault Recommendations */

/* Beginning of Fault Types */
insert into dbcc_types values 
(100000, "IO error", "IO error attempting to access this page.")


insert into dbcc_types values 
(100001, "page id error", "The page id, next page, or previous page value in the page header is invalid.")


insert into dbcc_types values 
(100002, "page free offset error", "The page free offset value in the page header is invalid.")


insert into dbcc_types values 
(100003, "page object id error", "The object/index id in the page header is invalid.")

insert into dbcc_types values 
(100004, "timestamp error", "The timestamp value in the page header is invalid.")


insert into dbcc_types values 
(100005, "wrong dbid error", "Allocation page is assigned to another database.")

insert into dbcc_types values 
(100006, "wrong object error", "The object/index id does not match the id in its allocation extent.")

insert into dbcc_types values 
(100007, "extent id error", "Extent allocated to an unknown object/index.")

insert into dbcc_types values 
(100008, "fixed format error", "Row format is inconsistent with page header.")

insert into dbcc_types values 
(100009, "row format error", "Either the attribute offset table, or the adjust table, is inconsistent.")

insert into dbcc_types values 
(100010, "row offset error", "The row offset table is inconsistent.")

insert into dbcc_types values 
(100011, "text pointer error", "Text column includes an invalid page reference.")

insert into dbcc_types values 
(100012, "wrong type error", "A page of wrong object type or format is linked to the chain.")

insert into dbcc_types values 
(100013, "non-OAM error", "Non-OAM page is linked to the OAM chain.")

insert into dbcc_types values 
(100014, "reused page error", "Page linked to more than one chain.")

insert into dbcc_types values 
(100015, "page loop error", "Page chain contains a loop.")

insert into dbcc_types values 
(100016, "page not used error", "Page allocated but not linked to the chain.")

insert into dbcc_types values 
(100017, "OAM ring error", "OAM ring chain is malformed.")

insert into dbcc_types values 
(100018, "missing OAM error", "OAM entry is missing for this allocation page.")

insert into dbcc_types values 
(100019, "extra OAM error", "OAM entry is present but there is no allocation.")

insert into dbcc_types values 
(100020, "check aborted error", "Check aborted because the chain could not be followed.")

insert into dbcc_types values 
(100021, "chain end error", "Chain does not end on the expected page.")

insert into dbcc_types values 
(100022, "chain start error", "Chain start reference disagrees with page linkage.")

insert into dbcc_types values 
(100023, "used count error", "OAM used pages count discrepancy exceeds threshold.")

insert into dbcc_types values 
(100024, "unused count error", "OAM unused pages count discrepancy exceeds threshold.")

insert into dbcc_types values 
(100025, "row count error", "OAM row count discrepancy exceeds threshold.")

insert into dbcc_types values 
(100026, "serialloc error", "Violation of the SERIALLOC rules on allocation page.")

insert into dbcc_types values 
(100027, "text root error", "First and root page of a text index must be the same.")

insert into dbcc_types values 
(100028, "page misplaced", "The page is not located in the objects's reserved region.")

insert into dbcc_types values 
(100029, "page header error", "Inconsistent status flags or values out of range.")

insert into dbcc_types values 
(100030, "page format error", "Page format errors on OAM or text pages.")

insert into dbcc_types values 
(100031, "page not allocated", "Page reached by the chain is not allocated.")

insert into dbcc_types values 
(100032, "page linkage error", "Invalid or inconsistent page chain linkages.")

insert into dbcc_types values 
(100033, "non-contiguous free space error", "Invalid or inconsistent value for the non-contiguous free space on the page.")

insert into dbcc_types values 
(100034, "insert free space error", "Invalid or inconsistent value for the contiguous free space on the page.")

insert into dbcc_types values 
(100035, "spacebits mismatch", "Inconsistency in the page fullness indicator.")

insert into dbcc_types values 
(100036, "deleted row count error", "Invalid or inconsistent value for the deleted row count on the page.")

insert into dbcc_types values 
(100037, "forwarded rows error", "Inconsistency between the forwarded rows indicator and the number of forwarded rows on the page.")

insert into dbcc_types values 
(100038, "page header type error", "Page header format indicator is set incorrectly.")

insert into dbcc_types values 
(100039, "incorrect extent oampage", "Extent OAM page reference is set incorrectly.")

insert into dbcc_types values 
(100040, "OAM page format error", "Non-first OAM page has non zero first OAM page specific data.")

insert into dbcc_types values
(100041, "Log free space discrepancy error", "Current syslogs free space does not match what dbcc counted.")

/* End of Fault Types */

/*
** Fill in the dbcc_reco table with fault_type/reco_type mappings.
*/
print "Inserting recommendation codes into the dbcc_reco table."

/* Insert fault_type/reo_type mappings in dbcc_reco table. */
insert into dbcc_reco values (100000, 40000)
insert into dbcc_reco values (100000, 40002)
insert into dbcc_reco values (100001, 40002)
insert into dbcc_reco values (100002, 30000)
insert into dbcc_reco values (100002, 40002)
insert into dbcc_reco values (100003, 20005)
insert into dbcc_reco values (100003, 20003)
insert into dbcc_reco values (100003, 40001)
insert into dbcc_reco values (100004, 30000)
insert into dbcc_reco values (100004, 40002)
insert into dbcc_reco values (100005, 40002)
insert into dbcc_reco values (100006, 20003)
insert into dbcc_reco values (100007, 40001)
insert into dbcc_reco values (100008, 20005)
insert into dbcc_reco values (100009, 20005)
insert into dbcc_reco values (100010, 20005)
insert into dbcc_reco values (100011, 30000)
insert into dbcc_reco values (100011, 40002)
insert into dbcc_reco values (100012, 30000)
insert into dbcc_reco values (100012, 40002)
insert into dbcc_reco values (100013, 30000)
insert into dbcc_reco values (100013, 40002)
insert into dbcc_reco values (100014, 40002)
insert into dbcc_reco values (100015, 30000)
insert into dbcc_reco values (100015, 40002)
insert into dbcc_reco values (100016, 20005)
insert into dbcc_reco values (100017, 40002)
insert into dbcc_reco values (100018, 20002)
insert into dbcc_reco values (100018, 20003)
insert into dbcc_reco values (100018, 40001)
insert into dbcc_reco values (100019, 20003)
insert into dbcc_reco values (100019, 40001)
insert into dbcc_reco values (100020, 20001)
insert into dbcc_reco values (100021, 20005)
insert into dbcc_reco values (100022, 20005)
insert into dbcc_reco values (100023, 20002)
insert into dbcc_reco values (100023, 20003)
insert into dbcc_reco values (100023, 40001)
insert into dbcc_reco values (100024, 20002)
insert into dbcc_reco values (100024, 20003)
insert into dbcc_reco values (100024, 40001)
insert into dbcc_reco values (100025, 20005)
insert into dbcc_reco values (100026, 40001)
insert into dbcc_reco values (100027, 30000)
insert into dbcc_reco values (100027, 40002)
insert into dbcc_reco values (100028, 20008)
insert into dbcc_reco values (100029, 20005)
insert into dbcc_reco values (100029, 30001)
insert into dbcc_reco values (100030, 30000)
insert into dbcc_reco values (100030, 40002)
insert into dbcc_reco values (100031, 20003)
insert into dbcc_reco values (100031, 40001)
insert into dbcc_reco values (100032, 30000)
insert into dbcc_reco values (100032, 40002)
insert into dbcc_reco values (100033, 20006)
insert into dbcc_reco values (100034, 20006)
insert into dbcc_reco values (100035, 20004)
insert into dbcc_reco values (100036, 20006)
insert into dbcc_reco values (100037, 20006)
insert into dbcc_reco values (100038, 30000)
insert into dbcc_reco values (100038, 40002)
insert into dbcc_reco values (100039, 20009)
insert into dbcc_reco values (100040, 20007)
insert into dbcc_reco values (100041, 20003)

go

/*
** Drop the stored procedure driver sprocs, if they exist.
*/

if ((select db_name()) = "dbccdb")
	dump tran dbccdb with truncate_only

else
	if ((select db_name()) = "dbccalt")
		dump tran dbccalt with truncate_only


if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_validatedb')
begin
	print "Dropping sp_validatedb"
	drop procedure sp_validatedb
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_getopid')
begin
	print "Dropping sp_getopid"
	drop procedure sp_getopid
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_alterws')
begin
	print "Dropping sp_dbcc_alterws"
	drop procedure sp_dbcc_alterws
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_createws')
begin
	print "Dropping sp_dbcc_createws"
	drop procedure sp_dbcc_createws
end

if ((select db_name()) = "dbccdb")
	dump tran dbccdb with truncate_only
else
	if ((select db_name()) = "dbccalt")
		dump tran dbccalt with truncate_only

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_configreport')
begin
	print "Dropping sp_dbcc_configreport"
	drop procedure sp_dbcc_configreport
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_deletehistory')
begin
	print "Dropping sp_dbcc_deletehistory"
	drop procedure sp_dbcc_deletehistory
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_deletedb')
begin
	print "Dropping sp_dbcc_deletedb"
	drop procedure sp_dbcc_deletedb
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_evaluatedb')
begin
	print "Dropping sp_dbcc_evaluatedb"
	drop procedure sp_dbcc_evaluatedb
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_updateconfig')
begin
	print "Dropping sp_dbcc_updateconfig"
	drop procedure sp_dbcc_updateconfig
end

if ((select db_name()) = "dbccdb")
	dump tran dbccdb with truncate_only
else
	if ((select db_name()) = "dbccalt")
		dump tran dbccalt with truncate_only

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_summaryreport')
begin
	print "Dropping sp_dbcc_summaryreport"
	drop procedure sp_dbcc_summaryreport
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_runcheck')
begin
	print "Dropping sp_dbcc_runcheck"
	drop procedure sp_dbcc_runcheck
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_statisticsreport')
begin
	print "Dropping sp_dbcc_statisticsreport"
	drop procedure sp_dbcc_statisticsreport
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_faultreport')
begin
	print "Dropping sp_dbcc_faultreport"
	drop procedure sp_dbcc_faultreport
end


if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_fullreport')
begin
	print "Dropping sp_dbcc_fullreport"
	drop procedure sp_dbcc_fullreport
end

if ((select db_name()) = "dbccdb")
	dump tran dbccdb with truncate_only
else
	if ((select db_name()) = "dbccalt")
		dump tran dbccalt with truncate_only

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_differentialreport')
begin
	print "Dropping sp_dbcc_differentialreport"
	drop procedure sp_dbcc_differentialreport
end


if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_recommendations')
begin
	print "Dropping sp_dbcc_recommendations"
	drop procedure sp_dbcc_recommendations 
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_patch_finishtime')
begin
	print "Dropping sp_dbcc_patch_finishtime"
	drop procedure sp_dbcc_patch_finishtime
end

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_exclusions')
begin
	print "Dropping sp_dbcc_exclusions"
	drop procedure sp_dbcc_exclusions 
end

if ((select db_name()) = "dbccdb")
	dump tran dbccdb with truncate_only
else
	if ((select db_name()) = "dbccalt")
		dump tran dbccalt with truncate_only

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_help_fault')
begin
	print "Dropping sp_dbcc_help_fault"
	drop procedure sp_dbcc_help_fault 
end
go

/*******************************************************************************
** Begin upgrade section. The following section contains SQL for each upgrade **
** of the dbcc database schema. Every time a new upgrade step is added the    **
** dbversion must be incremented at the end where we insert the dbversion in  **
** dbcc_control with the kename of DBVERSION.                                 **
*******************************************************************************/

/*
** Upgrade step for dbversion 1 here.
** Again validate the database. 
*/
declare @dbversion int

select @dbversion = dbversion from tempdb..dbcc_dbversion
if (@dbversion < 1) 
begin
	print "Upgrading dbcc_operation_log schema"
	exec ("alter table dbcc_operation_log add seq smallint null, id int null")
	print "Upgrading dbcc_operation_results schema"
	exec ("alter table dbcc_operation_results add seq smallint null")
end
go

/*
** Upgrade step for dbversion 5 here.
*/
declare @dbversion int

select @dbversion = dbversion from tempdb..dbcc_dbversion
if (@dbversion < 5)
begin
	print "Upgrading dbcc_faults schema"
	exec ("alter table dbcc_faults lock datarows")

	print "Upgrading dbcc_operation_results schema"
	exec ("alter table dbcc_operation_results lock datarows")

	print "Upgrading dbcc_operation_log schema"
	exec ("alter table dbcc_operation_log lock datarows")
	exec ("alter table dbcc_operation_log add maxseq smallint null")

	print "Upgrading dbcc_counters schema"
	exec ("alter table dbcc_counters modify partitionid int") 

	print "Upgrading dbcc_faults schema"
	exec ("alter table dbcc_faults modify partitionid int") 

	-- If not already done, exclude ex_forward bit mismatch for all DBs
	print "Upgrading default exclusion list"
	if not exists (select 1 from dbcc_exclusions 
			where dbid = 0 and type = 1 and fault_type = 100037
			  and table_name is NULL)
	begin
		insert into dbcc_exclusions values (0, 1, 100037, NULL)
		insert into dbcc_exclusions values (0, 1, 100040, NULL)
	end
end
go

/*
** Upgrade step for dbversion 6 here.
** Again validate the database. 
*/
declare @dbversion int

select @dbversion = dbversion from tempdb..dbcc_dbversion
if (@dbversion < 6) 
begin
	print "Upgrading dbcc_exclusions schema"
	exec ("alter table dbcc_exclusions modify table_name varchar(255) null")
end
go

/*******************************************************************************
** End of UPGRADE sections 						      **
** Then create all the indexes.                                               **
*******************************************************************************/

if exists  (select * from sysobjects where 
			name = 'dbcc_types' and type = 'U')
begin
	print "Creating index on dbcc_types table"
	create unique index dbcc_types_index on dbcc_types(type_code) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_config' and type = 'U')
begin
	print "Creating index on dbcc_config table"
	create unique index dbcc_config_index 
		on dbcc_config(dbid,type_code) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_dev_info' and type = 'U')
begin
	print "Creating index on dbcc_dev_info table"
	create unique index dbcc_dev_info_index 
		on dbcc_dev_info(devid) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_operation_log' and type = 'U')
begin
	print "Creating index on dbcc_operation_log table"
	create unique index dbcc_operation_log_index 
		on dbcc_operation_log(dbid,opid, optype, seq) 
end
if exists  (select * from sysobjects where 
		name = 'dbcc_operation_results' and type = 'U')
begin
	print "Creating index on dbcc_operation_results table"
	create unique index dbcc_operation_results_index 
		on dbcc_operation_results(dbid,opid, optype,type_code, 
					  seq) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_counters' and type = 'U')
begin
	print "Creating index on dbcc_counters table"
	create unique index dbcc_counters_index 
		on dbcc_counters(dbid,opid,id,indid,partitionid,
					devid,type_code) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_faults' and type = 'U')
begin
	print "Creating index on dbcc_faults table"
	create unique index dbcc_faults_index 
		on dbcc_faults(dbid,opid,id,indid,faultid,type_code) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_fault_params' and type = 'U')
begin
	print "Creating index on dbcc_fault_params table"
	create unique index dbcc_fault_params_index 
		on dbcc_fault_params(dbid,opid,faultid,type_code) 
end
if exists  (select * from sysobjects where 
			name = 'dbcc_exclusions' and type = 'U')
begin
	print "Creating index on dbcc_exclusions table"
	create unique clustered index dbcc_exclusions_index on 
		dbcc_exclusions(dbid, type, fault_type, table_name) 
	  with ignore_dup_key 
end
if exists  (select * from sysobjects where
			name = 'dbcc_reco' and type = 'U')
begin
	print "Creating index on dbcc_reco table"
	create clustered index dbcc_reco_index
		on dbcc_reco(fault_type)
end
go

/*
** Finally save the current dbversion in dbcc_control. 
** NOTE: this number must be changed every time a new upgrade step is added
**       above.
*/
insert dbcc_control(keyname, intvalue) values("DBVERSION", 6)

/* If no default configuration is present for scan and text workspaces
** and the default workspaces do not exist already  we will create 
** a default scan and text workspace of the minimum size, and configure 
** them to be the default, note that we do this only in dbccdb since defaults
** are not supported in dbccalt.
*/
if ((db_id('dbccdb') = db_id()) and
    (not exists (select * from dbcc_config 
		where dbid = 0 and type_code in (3,4))) and 
    (not exists (select * from sysobjects 
		where type = 'U' and name in ('def$scan$ws', 'def$text$ws'))))
begin
	print "Creating default workspaces"
	dbcc createws ('dbccdb', 'default', 'def$scan$ws', 'scan', 128)
	dbcc createws ('dbccdb', 'default', 'def$text$ws', 'text', 64)

	print "Configuring default workspaces"
	/* type 3 is for 'scan workspace' and 4 is for 'text workspace' */
	insert into dbcc_config values(0, 3, object_id('dbccdb..def$scan$ws'),
					'def$scan$ws')
	insert into dbcc_config values(0, 4, object_id('dbccdb..def$text$ws'),
					'def$text$ws')
	insert into dbcc_config values(0, 6, null,
					'DEFAULT_VALUES')
end
go

/*
** drop the temporary table dbcc_dbversion. We don't need it any more
*/
if (object_id('tempdb..dbcc_dbversion') is not null) 
begin
	drop table tempdb..dbcc_dbversion
end
go

checkpoint
go

go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_validatedb')
begin
	drop procedure sp_validatedb
end
go
print "Installing sp_validatedb"
go


/*
** Validate that the specified database exists. Return 1 if @dbname
** doesn't exist, elase return 0 and set it dbid in @dbid
** expects @dbname to be nonnul.
*/

/*
** Messages for "sp_validatedb"	
**  17421, "No such database -- run sp_helpdb to list databases."
**  18482, "Since dbccalt exists, it is the correct database for dbccdb. Use the dbccalt database."
*/
create procedure sp_validatedb 
( 
		@dbname  varchar(255),
		@dbid  int  output
)
as

declare  @id int                      /* dbid of the database */
declare  @dbccalt_dbid int

set nocount on

select @dbid = null
/*
**  Verify the database name and get the dbid of the database 
*/
if (@dbname is null)
begin
	return(1)
end
select @id = db_id(@dbname)
 
if @id is null
begin
	/*
	** 17421, "No such database -- run sp_helpdb to list databases."
	*/
	raiserror 17421
	return (1)
end
else
begin
	if (@dbname = "dbccdb")
	begin
		select @dbccalt_dbid = db_id("dbccalt")  
		if (@dbccalt_dbid is not null and @dbccalt_dbid != db_id())
		begin
			/*
			**  18482, "Since dbccalt exists, it is the correct database for dbccdb. Use the dbccalt database."
			*/
			raiserror 18482
			return(1)
		end
	end
end

select @dbid = @id
return (0)
go
grant execute on sp_validatedb to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_getopid')
begin
	drop procedure sp_getopid
end
go
print "Installing sp_getopid"
go


/*
** For the given database, optype and date, get the dbcc
** operation id from dbcc_operation_log table
*/

create procedure sp_getopid 
( 
		@dbname  varchar(30),
		@date  datetime = null,
		@optype int = null, 
		@opid int  output
)
as

declare  @id int
declare  @dbid int
declare  @date1 datetime

set nocount on

select @dbid = db_id(@dbname)
if (@dbname is null or @dbid is null) 
begin
	return (1)
end

if (@optype is null)
	/* This is the type code for checkstorage */
	select @optype = 2

if (@date is null)
begin
	select @date1 = max(finish) from dbcc_operation_log
		where dbid = @dbid and optype = @optype and start <= finish
end
else
begin
	select @date1 = max(finish) from dbcc_operation_log
		where dbid = @dbid and optype = @optype and start <= finish
			and finish <= @date
end

select @opid = opid from dbcc_operation_log 
		where dbid = @dbid and finish = @date1

if (@opid is null)
	return(1)
else
	return(0)
go
grant execute on sp_getopid to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_recommendations')
begin
	drop procedure sp_dbcc_run_recommendations
end
go
print "Installing sp_dbcc_run_recommendations"
go


/* 
** This is an internal dbcc sproc that is invoked by sp_dbcc_recommendations.
** Its purpose is to generate a list of administrative recommendations based
** on the results of a checkstorage run.
*/

/*
** Messages for "sp_dbcc_run_recommendations"
** 17260, "Can't run %1! from within a transaction."
** 18464, "%1! name cannot be null."
** 18291, "The parameter value '%1!' is invalid."
** 18998, "The checkstorage run corresponding to dbid '%1!', opid '%2!',
**	   reported no faults; therefore, no corrective action 
**	   is necessary."
** 18999, "An error occurred while fetching data from a temporary table.
**	   If there are no other error messages and this error persists,
**	   please contact Sybase Technical Support."
** 19020, "The checkstorage run corresponding to dbid '%1!', object name
**	   '%2!', opid '%3!', reported no faults; therefore, no corrective
**	   action is necessary."
** 19036, 'Contact Sybase Technical Support if the recommended
**         corrective action does not resolve the faults.'
** 19038, 'Suggested corrective actions for object %1!, objid %2!, 
** 	   listed in recommended order:'
** 19039, 'Recommended corrective action for object %1!, objid %2!:'
** 19040, 'Generating recommendations for database %1!.'
** 19041, 'Allocation page errors were found in database %1!.'
** 19042, 'Database level recommendations:'
** 19329, 'Suggested corrective actions for object %1! (id %2!), indid %3!, 
**         partition %4! (id %5!), listed in recommended order:'
** 19330, 'Recommended corrective action for object %1! (id %2!), indid %3!, 
**         partition %4! (id %5!):"
**
*/

create procedure sp_dbcc_run_recommendations
(
	@dbname 	varchar(255),
	@date 		datetime = null,
	@opid 		int = null,
	@objectname  	varchar(255) = null,
	@reco_by_ptn	tinyint = 0
)
as
declare @dbid 		int		/* The dbid of the subject database */
declare @status 	int		/* Return status from sp_validatedb */
declare @msg 		varchar(1024)	/* Message holder variable */
declare @msg_1 		varchar(80)
declare @msg_2 		varchar(80)
declare @separator 	varchar(80)	/* The section separator line */
declare @reco_msg_fmt 	varchar(40)	/* Format string for printing 
					** recommendations 
					*/
declare @type_code 	int
declare @id 		int		/* Id of object being reported on */
declare @indid 		int		/* Indid being reported on */
declare @partitionid 	int		/* Partition id being reported on */
declare @partition_name	varchar(255)	/* Partition name being reported on */
declare @recommendation varchar(255) 	/* recommendation text */
declare @count 		int		/* Counter for recos for an object */
declare @alloc_page_id 	int		/* The ALLOCATION object id (99) */
declare @min_db_reco    int		/* The minimum reco type for database 
					** level recos 
					*/
declare @min_user_reco  int		/* The minimum reco type for object 
					** level recos suitable for user tables
					** only.
					*/
declare	@reload_db_reco	int		/* The reco type for the 
					** 'reload db reco' 
					*/
declare @number_of_alloc_pg_faults int  /* The number of reported allocation
					** page (objid 99) faults.
					*/
declare @optype_for_checkstorage   int 	/* The optype for checkstorage is 2 */
declare @number_of_recommendations int 	/* Total number of recommendations
				       	** we will issue for a particular
				       	** object.
				       	*/

/* Initializations */ 
select 	@optype_for_checkstorage = 2 
select 	@alloc_page_id = 99

/*
** The recommendation types are codified such that all object level 
** recommendations have a type_code less than @min_db_reco and all database 
** level recommendations start from @min_db_reco. Object level recommendations
** are further divided into recos for all tables and recos for user tables only.
** Recommendations applicable to user tables only starts from @min_user_reco.
*/
select 	@min_db_reco 	= 40000,
	@min_user_reco	= 30000,
	@reload_db_reco = 40002
 
/*
** Disallow this procedure within a transaction since it
** creates temporary tables
*/
if (@@trancount > 0)
begin
	/*
	** 17260, "Can't run %1! from within a transaction."
	*/
	raiserror 17260, 'sp_dbcc_recommendations'
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1
set nocount on

/*
** Permission checking:
**      This sproc can be excuted by anyone; therefore, permission
**      checking is not necessary.
*/

/* Validate the database name */
if (@dbname is not null)
begin
	exec  @status = sp_validatedb  @dbname, @dbid output
	if ((@status = 1) or (@dbid is null))
	begin
		return(1)
	end
end
else
begin
	/*
	** 18464, "%1! name cannot be null."
	*/
	raiserror 18464, 'database'
	return(1)
end

/* 
** If both opid and date were given, use opid.  If opid was not given,
** use the date parameter to get the opid.
*/
if (@opid is null)
begin
        /*
        ** Get the opid for this database corresponding to the date
        ** specified or that of the most recent checkstorage operation. 
        */
	exec sp_getopid @dbname, @date, @optype_for_checkstorage,
			@opid output

	if (@opid is null)
	begin
		/* 
		** If we end up in here, we cannot continue, as there
		** is no checkstorage information to report on.
		*/
		if (@date is not NULL)
		begin
			/*
			** 18291, "The parameter value '%1!' is invalid."
			*/
			raiserror 18291, @date
		end
		else
		begin
			/* 18491, "No %1! on DBCC operations available." */
			raiserror 18491, 'recommendation report'
		end

		return(1)
	end

end
else
begin
	/*
	** Verify that the opid corresponds to the given db and represents
	** a checkstorage run.
	*/ 
	select @count = 
		count(*) from dbcc_operation_log
		where opid = @opid and dbid = @dbid
		and optype = @optype_for_checkstorage 
				  
	if (@count = 0)
	begin
                /*
                ** 18291, "The parameter value '%1!' is invalid."
                */
                raiserror 18291, @opid
                return(1)
        end
end	

/* 
** From dbcc_faults, get the information we need corresponding to the
** opid we are working on.
** If the user gave us an object name, they are interested in just that
** object so we will select info regarding that object only.
** If object_name(id, @dbid) is null then the object has been dropped
** since the checkstorage report hence we are not interested in such objects.
*/ 
select distinct id, indid, partitionid, type_code
  into #faults_by_objid
  from dbcc_faults
 where opid = @opid 
   and dbid = @dbid
   and object_name(id, @dbid) is not null
   and ((@objectname is null) or (object_name(id, @dbid) = @objectname))

/* Do we have any faults to generate recommendations for? */
select @count = count(*) from #faults_by_objid
if (@count = 0)
begin
	if (@objectname is not null)
	begin
		/*
		** 19020, "The checkstorage run corresponding to
		** dbid '%1!', object name '%2!', opid '%3!', reported
		** no faults; therefore, no corrective action is necessary."
		*/
		exec sp_getmessage 19020, @msg output
		print @msg, @dbid, @objectname, @opid
	end
	else
	begin
		/* 
		** 18998, "The checkstorage run corresponding to
		** dbid '%1!', opid '%2!', reported no faults;
		** therefore, no corrective action is necessary."
		*/
		exec sp_getmessage 18998, @msg output
		print @msg, @dbid, @opid
	end
	return (0)
end

/* 
** If faults were reported on objid '99' (allocation page), we set 
** @number_of_alloc_pg_faults to the number of these faults that were 
** reported and delete the corresponding rows in #faults_by_objid.
** Later in this report, we will check the @number_of_alloc_pg_faults
** variable, and, if it is not zero, we will print a message that tells
** the user that faults were reported on allocation pages belonging
** to the database we are reporting on. We will also add the @reload_db_reco 
** recommendation to the list of recommendations.
*/
select @number_of_alloc_pg_faults = count(*) from #faults_by_objid
	where id = @alloc_page_id

if (@number_of_alloc_pg_faults > 0)
begin
	delete from #faults_by_objid where id = @alloc_page_id
end

/* Now, map the recommendations to the corresponding faults */ 
select distinct T1.id, T1.indid, T1.partitionid, T2.reco_type
  into #fault_reco
  from #faults_by_objid as T1, dbcc_reco as T2 
 where T1.type_code = T2.fault_type 

/* We are done with #faults_by_objid, so drop it */
drop table #faults_by_objid

/* 
** If we have a non zero @number_of_alloc_pg_faults, we will be printing
** the @reload_db_reco recommendation hence delete this reco type from 
** #fault_reco to avoid repeating the same later. 
*/
if ((@number_of_alloc_pg_faults > 0) and 
    not exists (select 1 from #fault_reco where reco_type = @reload_db_reco))
begin
	insert into #fault_reco values (@alloc_page_id, 0, @alloc_page_id, 
					@reload_db_reco) 
end

/* Print out the recommendations for the user */
print ''

select @separator = replicate ('=', 80), @reco_msg_fmt = '   %1!' 

/* 19040, 'Generating recommendations for database %1!.' */
exec sp_getmessage 19040, @msg output
print @msg, @dbname 
print @separator
print ''

/* If we have database level recommendations, print them first.  */
if exists (select 1 from #fault_reco where reco_type >= @min_db_reco)
begin
	/*
	** If allocation page errors were found by checkstorage, we will
	** state the same here.
	*/
	if (@number_of_alloc_pg_faults > 0)
	begin
		/* 
		** 19041, 'Allocation page errors were found in database %1!.' 
		*/
		exec sp_getmessage 19041, @msg output
		print @msg, @dbid 

		print  '' 
	end

	/* 19042, 'Database level recommendations:' */
	exec sp_getmessage 19042, @msg output
	print @msg
	print ''

	/* Declare cursor for database level recommendations. */
	declare cursor_dbcc_db_reco cursor for 
		select distinct t.type_code, t.description 
		from #fault_reco r,  dbcc_types t
		where r.reco_type >= @min_db_reco
		  and r.reco_type = t.type_code
		order by t.type_code

	open cursor_dbcc_db_reco  

	fetch cursor_dbcc_db_reco into @type_code, @recommendation
	while (@@sqlstatus = 0)
	begin
		print @reco_msg_fmt, @recommendation
		print '' 
		fetch cursor_dbcc_db_reco into @type_code, @recommendation
	end
	print @separator
	print ''

	/* Clean up */
	close cursor_dbcc_db_reco
	deallocate cursor cursor_dbcc_db_reco
end

/* 
** Get rid of database level recommendations and unsuitable recommendations 
** for system catalogs.
*/
delete #fault_reco 
 where reco_type >= @min_db_reco
    or (id < 100 and reco_type >= @min_user_reco) 

/* 
** If we don't want to print partition wise recommendations set all 
** indid, partitionid to 0
*/
if (@reco_by_ptn = 0)
begin
	update #fault_reco set indid = 0, partitionid = 0
end


/* If we have object level recommendations, print them now. */
if exists (select 1 from #fault_reco where reco_type < @min_db_reco)
begin
	/* Declare cursor for object level recommendations. */
	declare cursor_dbcc_obj_reco cursor for 
		select distinct r.id, r.indid, r.partitionid, 
				r.reco_type, t.description
		from #fault_reco r, dbcc_types t
		where r.reco_type < @min_db_reco
		  and r.reco_type = t.type_code
		order by r.id, r.indid, r.partitionid, r.reco_type

	open cursor_dbcc_obj_reco  

	/* Initial fetch */
	fetch cursor_dbcc_obj_reco into @id, @indid, @partitionid, @type_code, 
					@recommendation
	while (@@sqlstatus = 0)
	begin
		/* 
		** Get the number of recommendations we will issue for this 
		** object. 
		*/
		select @number_of_recommendations = count(distinct reco_type) 
		  from #fault_reco
		 where id = @id 
		   and indid = @indid
		   and partitionid = @partitionid
		   and reco_type < @min_db_reco

		select 	@count = @number_of_recommendations, 
			@objectname = object_name(@id, @dbid)

		if ((@reco_by_ptn = 0) and (@number_of_recommendations > 1))
		begin
			/* 
			** 19038, 'Suggested corrective actions for object %1!,
			** objid %2!, listed in recommended order:'
			*/
			exec sp_getmessage 19038, @msg output
			print @msg, @objectname, @id
		end
		else if (@reco_by_ptn = 0)
		begin
			/*
			** 19039, Recommended corrective action for object %1!, 
			** objid %2!:
			*/
			exec sp_getmessage 19039, @msg output
			print @msg, @objectname, @id
		end
		else 
		begin
			if (@number_of_recommendations > 1)
			begin
				/* 
				** 19329, 'Suggested corrective actions for 
				** object %1! (id %2!), indid %3!, partition 
				** %4! (id %5!), listed in recommended order:'
				*/
				exec sp_getmessage 19329, @msg output
			end
			else
			begin
				/*
				** 19330, 'Recommended corrective action for 
				** object %1! (id %2!), indid %3!, partition 
				** %4! (id %5!):"
				*/
				exec sp_getmessage 19330, @msg output
			end
			select @partition_name = 
				partition_name(@indid, @partitionid, @dbid)
			print @msg, @objectname, @id, @indid, @partition_name, 
			      @partitionid
		end

		print '' 

		while ((@@sqlstatus = 0) and (@count > 0))
		begin
			print @reco_msg_fmt, @recommendation
			fetch cursor_dbcc_obj_reco 
				into @id, @indid, @partitionid, @type_code, 
				     @recommendation
			select @count = @count -1
		end

		print '' 
	end

	/* Clean up */
	close cursor_dbcc_obj_reco
	deallocate cursor cursor_dbcc_obj_reco
end

print @separator
print ''

/*
** 19036, 'Contact Sybase Technical Support if the recommended
** corrective action does not resolve the faults.'
*/
exec sp_getmessage 19036, @msg output
print @msg
print ''

drop table #fault_reco

/* Did we have any problems fetching rows from dbcc_fault_reco? */
if (@@sqlstatus = 1)
begin
        /*
        ** 18999, "An error occurred while fetching data from a temporary
	** table. If there are no other error messages and this error 
	** persists, please contact Sybase Technical Support."
        */
        raiserror 18999 
        return (1)
end

return (0)
go
grant execute on sp_dbcc_run_recommendations to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_alterws')
begin
	drop procedure sp_dbcc_run_alterws
end
go
print "Installing sp_dbcc_run_alterws"
go

/*
** Messages for "sp_dbcc_run_alterws"	
**
** 17421, "No such database -- run sp_helpdb to list databases."
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 18531, "You must execute this procedure from the database 
** 	  in which you wish to create or alter workspaces.  Execute 
**	  'use %1!' and try again."
** 18463, "%1! is not a valid DBCC database. Database must be either dbccdb or dbccalt"
** 18464, "%1! name cannot be null." 
** 18460, "%1! is not a valid workspace in %2! database."
** 18466, "Workspace size must be a valid number and must be at least %1!KB"
** 18461, "Workspace %1! in %2! database could not be altered to size %3!KB"
** 18462, "Workspace %1! has been altered successfully to size %2!KB",
*/
create procedure sp_dbcc_run_alterws 
( 
		@dbname  varchar(255), 
		@wsname  varchar(255),
		@wssize  varchar(15)
)
as
declare  @dbid int 			/* dbid of the database */
declare  @dbo int                     /* id of the owner of the database */
declare  @size int			/* requested size of ws in KB */
declare  @size_in_pages int 		/* requested size of ws in pages */
declare	 @new_size_in_pages int		/* altered size of ws in pages */
declare  @min_wssize int		/* minimum size of workspace */
declare  @min_rowlen int		/* minimum row length of workspace */
declare  @KB_per_page  int		/* number of KB in a page */
declare  @ret int
declare  @unit_loc int			/* unit of workspace size.(K, M, etc.) */
declare  @msg varchar(1024) 		/* a buffer for messages */
declare  @wstype varchar(5) 		/* type of workspace */
declare  @rtrn_status  int
declare  @dummy int

if @@trancount = 0
begin
        set chained off
end
 
set transaction isolation level 1
set nocount on

/*
**  Verify the database name and get the @dbid 
*/
if (@dbname != 'dbccdb' and @dbname != 'dbccalt')
begin
	if (@dbname is null)
	begin
		/*
		** 18464, "%1! name cannot be null." 
		*/
        raiserror 18464, "DBCC database"
	end
	else
	begin
		/*
		** 18463, "%1! is not a valid DBCC database. Database must be either dbccdb or dbccalt"
		*/
       	raiserror 18463, @dbname
	end
	return (1)
end

/*
**  Make sure that we are in the database specified
**  by @dbname. 
*/
if @dbname != db_name()
begin
	/* 
	** 18531, "You must execute this procedure from the database 
	** 	  in which you wish to create or alter workspaces.  
	**	  Execute 'use %1!' and try again."
	*/
	exec sp_getmessage 18531, @msg out
	raiserror 18531 @msg, @dbname
	return (1)
end

select @dbid = dbid
        from master.dbo.sysdatabases
                where name = @dbname

exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname
	 
/*
**  If @dbname not found, say so and return
*/
if @dbid is null
begin
	/*
	** 17421, "No such database -- run sp_helpdb to list databases."
	*/
	raiserror 17421
	return (1)
end

/*
**  Only the Database Owner (DBO) or
**  Accounts with SA role can execute it.
*/
if ((@dbo != 1) and (charindex("sa_role", show_role()) < 1))
begin
	/*
	** Call proc_role() to generate the appropriate
	** audit record.
	*/
	select @dummy = proc_role("sa_role")
	/*
	** 17230, "You must be the System Administrator (SA) or
	** the Database Owner (dbo) to execute this procedure."
	*/
	raiserror 17230
	return (1)
end

/*
** Verify that specified workspace exists
*/
if (@wsname is null)
begin
	/*
	** 18464, "%1! name cannot be null." 
	*/
	raiserror 18464, "Workspace"
	return(1)
end

/*
** A valid workspace will have entries in sysobjects and sysindexes table
** and its minimum and and maximum row length will be equal to 18 or 22
** (scan and text workspace)
** subtract 2 from minlen to adjust for the row header
*/
select  @min_rowlen = 0 
select @min_rowlen = (si.minlen - 2) from sysindexes si, sysobjects so
	 where  si.id = so.id and si.indid = 0 and so.name = @wsname 
		and si.minlen = si.maxlen 
if (@min_rowlen != 18 and @min_rowlen != 22)
begin
	/*
	** 18460, "%1! is not a valid workspace in %2! database."
	*/
	raiserror 18460, @wsname, @dbname
	return (1)
end
else
begin
	if (@min_rowlen = 18)
		select @wstype = 'scan'
	else
		select @wstype = 'text'
end

/* get the number of KB per page */
select @KB_per_page = @@maxpagesize / 1024
/*
**  validate @wssize. It must be a minimum of 24 pages
*/
select @min_wssize = 24 * @KB_per_page
select  @ret = 0
select @size = 0
if  @wssize is not null 
begin
	select @unit_loc = patindex("%[kKmMgGpP]%", @wssize)
	/*
	**  If the units have been specified grab them, otherwise default to
	**  units of pages.
	*/
	if @unit_loc = 0
	begin
		select  @wssize = @wssize + 'P'
	end
	
	/*
	** sp_aux_getsize returns size in KB
	*/
	exec @ret = sp_aux_getsize @wssize, @size output
end

if ((@wssize is null) or (@size < @min_wssize))
begin
	/*
	** 18466, "Workspace size must be a valid number and must be at least %1!KB"
	*/
	raiserror 18466, @min_wssize
	return (1)
end

/*
** Convert the size in KB to number of pages
*/
select @size_in_pages = @size / @KB_per_page

/*
** The size must be a multiple of 8. If not change @size_in_pages to the
** next multiple of 8
*/
if ((@size_in_pages % 8) != 0)
begin
	select @size_in_pages = ((@size_in_pages/8) + 1) * 8
end

select @rtrn_status = 1

dbcc alterws (@dbname, @wsname, @wstype, @size_in_pages)

if (@@error = 0)
begin
	/*
	** Get the size (in pages) of the altered workspace
	*/
	select @new_size_in_pages = data_pages(@dbid, id, indid)
	from syspartitions
	where id = object_id(@wsname)
	  and indid = 0
	
	if (@new_size_in_pages != @size_in_pages)
	begin
		/*
		** 18461, "Workspace %1! in %2! database could not be altered to size %3!KB"
		*/
		raiserror 18461, @wsname, @dbname, @size
	end
	else
	begin
	
		/*
		** 18462, "Workspace %1! has been altered successfully to size %2!KB",
		*/
		exec sp_getmessage 18462, @msg output
		print @msg, @wsname, @size
		select @rtrn_status = 0
	end
end

return ( @rtrn_status )
go
grant execute on sp_dbcc_run_alterws to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_createws')
begin
	drop procedure sp_dbcc_run_createws
end
go
print "Installing sp_dbcc_run_createws"
go

/*
** Messages for "sp_dbcc_run_createws"
**
** 17421, "No such database -- run sp_helpdb to list databases."
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 17520, "There is no such segment as '%1!'."
** 18531, "You must execute this procedure from the database 
** 	  in which you wish to create or alter workspaces.  Execute 
**	  'use %1!' and try again."
** 18463, "%1! is not a valid DBCC database. Database must be either dbccdb or dbccalt."
** 18464, "%1! name cannot be null." 
** 18465, "%1! is not a valid workspace type. Valid types are 'scan' and 'text'."
** 18466, "Workspace size must be a valid number and must be at least 32K or 16 pages."
** 18467, "Workspace %1! already exists in %2! database."
** 18468, "Workspace %1! could not be created in %2! database."
** 18469, "Workspace %1! of %2!KB size has been created successfully in %3! database."
*/
create procedure sp_dbcc_run_createws 
( 
		@dbname  varchar(255), 
		@segname  varchar(255),
		@wsname  varchar(255) = null,
		@wstype  varchar(30),
		@wssize  varchar(15)
)
as
declare  @dbid int 			/* dbid of the database */
declare  @dbo int                     /* id of the owner of the database */
declare  @ws_prefix varchar(30)
declare  @ws_fullname varchar(255)
declare  @count int
declare  @ret int
declare  @size int			/* requested size of ws in KB */
declare  @size_in_pages int 		/* requested size of ws in pages */
declare  @min_wssize int		/* minimum size of workspace */
declare  @KB_per_page  int		/* number of KB in a page */
declare  @unit_loc int			/* unit of workspace size.(K, M, etc.) */
declare  @msg varchar(1024) 		/* a buffer for messages */
declare  @dummy int

if @@trancount = 0
begin
        set chained off
end
 
set transaction isolation level 1
set nocount on

/*
**  Verify the database name and get the @dbid
*/
if (@dbname != 'dbccdb' and @dbname != 'dbccalt')
begin
	if (@dbname is null)
	begin
		/*
		** 18464, "%1! name cannot be null." 
		*/
        raiserror 18464, "DBCC database"
	end
	else
	begin
		/*
		** 18463, "%1! is not a valid DBCC database. Database must be 
		** either dbccdb or dbccalt"
		*/
		raiserror 18463, @dbname
	end
	return (1)
end

/*
**  Make sure that we are in the database specified
**  by @dbname. 
*/
if @dbname != db_name()
begin
	/* 
	** 18531, "You must execute this procedure from the database 
	** 	  in which you wish to create or alter workspaces.  
	**	  Execute 'use %1!' and try again."
	*/
	exec sp_getmessage 18531, @msg out
	raiserror 18531 @msg, @dbname
	return (1)
end

select @dbid = dbid
        from master.dbo.sysdatabases
                where name = @dbname

/*
**  If @dbname not found, say so and return
*/
if @dbid is null
begin
	/*
	** 17421, "No such database -- run sp_helpdb to list databases."
	*/
	raiserror 17421
	return (1)
end

exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname
	 
/*
**  Only the Database Owner (DBO) or
**  Accounts with SA role can execute it.
*/
if ((@dbo != 1) and (charindex("sa_role", show_role()) < 1))
begin
	/*
	** Call proc_role() to generate the appropriate
	** audit record.
	*/
	select @dummy = proc_role("sa_role")
	/*
	** 17230, "You must be the System Administrator (SA) or
	** the Database Owner (dbo) to execute this procedure."
	*/
	raiserror 17230
	return (1)
end

if not exists (select * from syssegments
		where name = @segname)
begin
	if (@segname is null)
	begin
		/*
		** 18464, "%1! name cannot be null." 
		*/
		raiserror 18464, "Segment"
	end
	else
	begin
		/*
		** 17520, There is no such segment as '%1!'.
		*/
		raiserror 17520, @segname
	end
	return (1)
end

/*
**  validate @wstype. It must be either "text" or "scan"
*/

if (@wstype != 'scan' and @wstype != 'text')
begin
	if (@wstype is null)
	begin
		/*
		** 18464, "%1! name cannot be null." 
		*/
		raiserror 18464, "Workspace type"
	end
	else
	begin
		/*
		** 18465, "%1! is not a valid workspace type. Valid types are 'scan' and 'text'"
		*/
		raiserror 18465, @wstype
	end
	return (1)
end

/* get the number of KB per page */
select @KB_per_page = @@maxpagesize / 1024

/*
**  validate @wssize. It must be a minimum of 24 pages
*/
select @min_wssize = 24 * @KB_per_page
select  @ret = 0
select @size = 0
if  @wssize is not null 
begin
	select @unit_loc = patindex("%[kKmMgGpP]%", @wssize)
	/*
	**  If the units have been specified grab them, otherwise default to
	**  units of pages.
	*/
	if @unit_loc = 0
	begin
		select  @wssize = @wssize + 'P'
	end
	
	/*
	** sp_aux_getsize returns size in KB
	*/
	exec @ret = sp_aux_getsize @wssize, @size output
end

if ((@wssize is null) or (@size < @min_wssize))
begin
	/*
	** 18466, "Workspace size must be a valid number and must be at 
	** least %1!KB"
	*/
	raiserror 18466, @min_wssize
	return (1)
end

/*
** The size_in_pages must be a multiple of 8. If not, 
** change @size_in_pages to the next multiple of 8
*/
select @size_in_pages = @size / @KB_per_page
if ((@size_in_pages % 8) != 0)
begin
	select @size_in_pages = ((@size_in_pages/8) + 1) * 8
end

if (@wsname is null)
begin
	/*
	**  construct a workspace name in the format scan_ws_nnnnnn or
	**  text_ws_nnnnnn where nnnnnn is a numerical string.
	*/
	select @ws_prefix = @wstype + "_ws"
	select @count = 0
	select @ws_fullname = @ws_prefix +
			right("000000" + convert (varchar(6), @count), 6)
 
	while exists (select * from sysobjects
		where type = 'U' and name = @ws_fullname)
	begin
		select @count = @count + 1
		select @ws_fullname = @ws_prefix +
	        	right("000000" + convert (varchar(6), @count), 6)
	end
	select @wsname = @ws_fullname
end
else
begin
	if exists (select * from sysobjects
		where type = 'U' and name = @wsname)
	begin
		/*
		** 18467, "Workspace %1! already exists in %2! database",
		*/
		raiserror 18467, @wsname, @dbname
		return (1)
	end
end

dbcc createws (@dbname, @segname, @wsname, @wstype, @size_in_pages)

/*
** verify that workspace has been created. We need to do this
** because dbcc does not return any status
*/
if exists (select * from sysobjects
	where type = 'U' and name = @wsname)
begin
	/*
	** 18469, "Workspace %1! of %2!KB size has been created successfully in %3! database"
	*/
	exec sp_getmessage 18469, @msg output
	print @msg, @wsname, @size, @dbname
	return (0)
end
else
begin
	/*
	** 18468, "Workspace %1! could not be created in %2! database",
	*/
	raiserror 18468, @wsname, @dbname
	return (1)
end

go
grant execute on sp_dbcc_run_createws to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_configreport')
begin
	drop procedure sp_dbcc_run_configreport
end
go
print "Installing sp_dbcc_run_configreport"
go

/*
** Messages for "sp_dbcc_run_configreport"  
** 17260, "Can't run %1! from within a transaction."
** 17431, "true" 
** 17432, "false" 
** 18483, "Reporting Configuration information of database %1!."
** 18484, "No configuration information available for %1! database."
**
*/
create procedure sp_dbcc_run_configreport 
( 
		@dbname  varchar(255) = null,
		@defaults  varchar(5) = 'false' 
)
as
declare  @dbid smallint 		/* dbid of the database */
declare  @dbsize varchar(13) 		/* size of the database in KBs */
declare  @id int 			
declare  @len int, @padlen int 
declare  @maxlen1 int, @maxlen2 int
declare  @param_name  varchar(50) 
declare  @type_name  varchar(30) 
declare	 @strval1  varchar(50), @strval2  varchar(30)
declare  @sqlstatus1 int
declare  @msg varchar(1024) 
declare  @rtrn_status int
declare  @KB_per_page int
declare  @true_msg		varchar(10) /* the localized message 'true' */
declare  @false_msg		varchar(10) /* the localized message 'false' */
declare  @ws_configured 	int
declare  @cache_configured 	int
declare  @oamcnt_configured 	int
declare  @defstr	varchar(10)
declare  @isdefstr	varchar(10)
declare  @prdefstr	varchar(60)
declare  @orig_dbname	varchar(255) 
declare  @ws_dbid 	int
declare  @scratchdb sysname
declare  @qrystr varchar(256)
declare  @adbstat int
declare  @adb_dbid int

select @defstr = '** Default'
select @orig_dbname = @dbname
/* validate @defaults */
/* 17431, "true" */
exec sp_getmessage 17431, @true_msg out
/* 17432, "false" */
exec sp_getmessage 17432, @false_msg out
select @defaults = lower(@defaults)
if (@defaults not in ('true', 'false', @true_msg, @false_msg))
begin
        /*
        ** 19022, "The '%1!' parameter must be 'true' or 'false'."
        */
        raiserror 19022, "@defaults"
        return (1)
end

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        raiserror 17260, "sp_dbcc_configreport"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

select @adbstat=number from master.dbo.spt_values
where name="archive database"

create table  #dbcc_db_list(dbid  smallint, dbname  varchar(255))
declare cursor_dbcc_db_list cursor  
	for select  dbid, dbname from #dbcc_db_list order by dbid

/*
**  insert dbname and dbid for all databases configured in dbcc_config
**  in #dbcc_db_list
*/

if (@dbname is not null)
begin
	/*
	** If the dbname is valid, delete other databases from
	** #dbcc_db_list. Otherwise return error
	** because we are interested in only one database
	*/
	select @dbid = db_id(@dbname)
    	if (@dbid is null)
	begin
		/*
		** 17421, "No such database -- run sp_helpdb to list databases."
		*/
		raiserror 17421
		return (1)
	end
	else
		insert #dbcc_db_list(dbid, dbname) values(@dbid, @dbname)
end
else
begin
	/*
	** If @defaults is true we only want default values.
	** else we want all databases (including default)
	*/
	insert #dbcc_db_list(dbid, dbname) values (0, 'DEFAULT_VALUES')

	if (@defaults in ('false', @false_msg))
	begin
		insert #dbcc_db_list(dbid, dbname) 
		select d.dbid, d.name 
		from master..sysdatabases d
		where exists (select 1 from dbcc_config c 
				where c.dbid = d.dbid)
	end
end

create table  #dbcc_types(type_name varchar(30), type_code int)

/* Get the types relevant for configuration paramaters */ 
insert into #dbcc_types(type_name, type_code)
	select convert(varchar(30), t.type_name), t.type_code 
	from dbcc_types t
	where t.type_code < 1000

create table  #config_report(param_name varchar(30), strval1 varchar(50) null,
			strval2 varchar(12) null, 
			isdefstr varchar(10) default "")
declare cursor_config_report cursor  
	for select  param_name, strval1, strval2, isdefstr from #config_report 

select @KB_per_page = @@maxpagesize / 1024
select @ws_dbid = db_id()

/* we will set @rtrn_status to 0 when we print the output */
select @rtrn_status = 1

/*
** For each database in the list, generate a configuration report
*/
open cursor_dbcc_db_list
fetch cursor_dbcc_db_list into @dbid, @dbname
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0) 
begin
	print  ""
	print  ""
	if (@dbid = 0)
	begin
		/*
		** 19025, "Reporting Default DBCC configuration information."
		*/
		exec sp_getmessage 19025, @msg output
		print @msg

		select @dbsize = ""
	end
	else
	begin
		/*
		** 18483, "Reporting Configuration information of database %1!"
		*/
		exec sp_getmessage 18483, @msg output
		print @msg, @dbname

		/* 
		** For a regular database get database size. 
		** If it's an archive database, the disk map has to be
		** found in sysaltusages in the scratch database.
		*/
		if exists (select * from master.dbo.sysdatabases
			where dbid = @dbid
			and (status3 & @adbstat) = @adbstat)
		begin
			/*
			** The original diskmap is stored in the sysaltusages catalog
			** in the scratch database with a location = 4.
			** Read the scratch database name from sysattributes first.
			*/
			select @scratchdb = convert(sysname, char_value)
			from master.dbo.sysattributes
			where   class=28
				and object_type='D'
				and object=@dbid
				and attribute=0

			select @qrystr =
			'select @dbsize = ' +
			'convert(varchar(12), ' +
			'convert(numeric(12, 0), sum(size)) * @KB_per_page) + '+
			'''K'' from ' + @scratchdb + '.dbo.sysaltusages '+
			'where dbid=@dbid and location = 4'
			exec (@qrystr)
		end
		else
		begin
		select @dbsize = convert(varchar(12),
					convert(numeric(12, 0),
					sum(size)) * @KB_per_page) 
					+ 'K'
			from master..sysusages where dbid = @dbid
		end
	end
	print  ""
	
	/*
	** Get the parameter names and their values (as strings)
	** from dbcc_config table. For type_codes 2, 3, 4, 6 
	** (private cache, scan workspace and text workspace and 
	** database name). We need to calculate the size and 
	** convert it into KB.
	*/
	select @type_name =  convert(varchar(30), type_name)
		 from dbcc_types where type_code = 6

	insert #config_report(param_name, strval1, strval2) 
		select @type_name, convert(varchar(30), c.stringvalue), @dbsize
			from  dbcc_config c
			where c.dbid = @dbid and c.type_code = 6  
			and c.stringvalue is not null 

	/* get cache  name and size */
	select @type_name =  convert(varchar(30), type_name)
		 from dbcc_types where type_code = 2
	insert #config_report(param_name, strval1, strval2) 
		select @type_name, convert(varchar(30), c.stringvalue), 
			convert(varchar(10), c.value) + "K"
			from  dbcc_config c  
			where c.dbid = @dbid and c.type_code = 2  
			and c.stringvalue is not null 
			and c.value is not null
	select @cache_configured = @@rowcount

	/*
	**
	** NOTE_ABOUT_BUILT_INS: 
	**
	** This is with reference to the new builtins data_pages and
	** reserved_pages.
	**
	** This note is referenced in other stored procedures as well
	** using the above reference string (NOTE_ABOUT_BUILT_INS).
	**
	** Note that in the following insert statement, original
	** code was passing in dataoampg to the now retired data_pgs
	** builtin. This was true even in cases where indid > 1 (i.e case
	** where the dataoampg was 0. In such a case the builtin would return
	** 0. With the new builtin, data_pages (also reserved_pages), this
	** approach does not work since if an indid > 0 is passed to the
	** new builtin we take that to mean that index pages are 
	** required. To work around this the use of the case statement is
	** used so that we a) conditionally call the builtins and b)
	** pass in the appropriate indid argument to the builtin depending
	** on what we want (data pages, or index pages).
	**
	** In case of a) there is no point in calling the builtin for
	** cases where indid > 1 and we are passing in dataoampg since
	** the result is guaranteed to be 0. In fact it would be erroneous
	** to do so for indid > 1 since in that case the data_pages 
	** builtin would actually return the index pages (i.e. the builtin
	** will internally use the indoampg.
	**
	** In case b) if we want the data pages in the case of a clustered
	** index we have to pass in an indid of 0 to the builtin to indicate
	** that. Otherwise, the builtin would assume that the index
	** pages are desired.
	**
	**
	*/

	/* get workspace name and size */
	insert #config_report(param_name, strval1, strval2) 
		select distinct t.type_name, c.stringvalue + " (id = " +
			convert(varchar(12), object_id(c.stringvalue)) +")",
			convert(varchar(10), 
				case 
				  when si.indid <= 1 
				  then (data_pages (@ws_dbid,
						    object_id(c.stringvalue),
						    case 
							when si.indid = 1 
							then 0 
							else si.indid
						    end))
				  else 0
				end
					* @KB_per_page) + "K" 
			from #dbcc_types t, dbcc_config c, sysindexes si
			where c.dbid = @dbid and c.type_code = t.type_code
			and (c.type_code = 3 or c.type_code = 4)
			and si.id = object_id(c.stringvalue)
			and c.stringvalue is not null 
	select @ws_configured = @@rowcount

	/* Get OAM count threshold in % */
	select @type_name =  convert(varchar(30), type_name)
		 from dbcc_types where type_code = 7
	insert #config_report(param_name, strval1, strval2) 
		select @type_name, null, 
			convert(varchar(10), c.value) + "%"
			from  dbcc_config c  
			where c.dbid = @dbid and c.type_code = 7  
			and c.value is not null
	select @oamcnt_configured = @@rowcount

	/* Get rest of the parameters */
	insert #config_report(param_name, strval1, strval2) 
		select distinct convert(varchar(30), t.type_name), null,
			convert(varchar(10), c.value)
			from #dbcc_types t, dbcc_config c  
			where c.dbid = @dbid and c.type_code = t.type_code
			and c.type_code != 2  and c.type_code != 3
			and c.type_code != 4  and c.type_code != 6
			and c.type_code != 7 and c.value is not null

	/* 
	** Get default configuration if any for the parameters that are not 
	** explicitly configured for a database. 
	*/
	if (@dbid != 0)
	begin
		if (@cache_configured = 0)
		begin
			/* get default cache  name and size */
			select @type_name =  convert(varchar(30), type_name)
				 from dbcc_types where type_code = 2
			insert #config_report(param_name, strval1, strval2, 
						isdefstr) 
				select 	@type_name, 
					convert(varchar(30), c.stringvalue), 
					convert(varchar(10), c.value) + "K", 
					@defstr
				from  dbcc_config c  
				where c.dbid = 0 and c.type_code = 2  
				and c.stringvalue is not null 
				and c.value is not null
		end

		if (@ws_configured < 2)
		begin
			/* See note tagged by NOTE_ABOUT_BUILT_INS in this file */
			/* get workspace name and size */
			insert #config_report(param_name, strval1, strval2, 
					      isdefstr) 
				select distinct t.type_name, 
					c.stringvalue + " (id = " +
					convert(varchar(12), 
						object_id(c.stringvalue)) +")",
					convert(varchar(10), 
						  case
						    when si.indid <= 1
						    then
							(data_pages(
						    	   @ws_dbid,
						    	   object_id(c.stringvalue), 
							   case 
                                                             when si.indid = 1 
                                                             then 0 
                                                             else si.indid
                                                	   end
							)) 
						    else 0
						  end
						   * @KB_per_page) + "K",
					@defstr
				from #dbcc_types t, dbcc_config c, sysindexes si
				where c.dbid = 0 
				and c.type_code = t.type_code
				and c.type_code in (3,4)
				and si.id = object_id(c.stringvalue)
				and c.stringvalue is not null 
				and c.type_code not in (select type_code 
							  from dbcc_config c1
							 where dbid = @dbid 
							   and type_code in 
								(3,4))
		end

		if (@oamcnt_configured = 0)
		begin
			/* Get default OAM count threshold in % */
			select @type_name =  convert(varchar(30), type_name)
				 from dbcc_types where type_code = 7
			insert #config_report(param_name, strval1, strval2, 
						isdefstr) 
				select @type_name, null, 
					convert(varchar(10), c.value) + "%", 
					@defstr
				from  dbcc_config c  
				where c.dbid = 0 and c.type_code = 7  
				and c.value is not null
		end

		/* get rest of the default configurations */
		insert #config_report(param_name, strval1, strval2, isdefstr) 
			select distinct convert(varchar(30), t.type_name), null,
				convert(varchar(10), c.value), @defstr
			from #dbcc_types t, dbcc_config c  
			where c.dbid = 0 and c.type_code = t.type_code
			and c.type_code not in (2,3,4,5,6,7)
			and c.value is not null
			and c.type_code not in ( select type_code 
						   from dbcc_config c1
						  where dbid = @dbid 
						    and type_code not in 
							(2,3,4,5,6,7))
	end

	/*
	** Format the title
	*/
	select @maxlen1 = max(datalength(param_name)) + 2 from #config_report
	select @param_name = "Parameter Name"
	select @len = datalength(@param_name)
	select @padlen = @maxlen1 - @len
	select @param_name = @param_name + replicate(" ", @padlen)

	select @maxlen2 = max(datalength(strval1)) + 2 from #config_report
	select @strval1 = "Value"
	select @len = datalength(@strval1)
	select @padlen = @maxlen2 - @len
	select @strval1 = @strval1 + replicate(" ", @padlen)
	print  " %1! %2! %3!", @param_name, @strval1, "Size"
	print  ""

	open cursor_config_report
	fetch cursor_config_report into @param_name, @strval1, 
				@strval2, @isdefstr
	while (@@sqlstatus = 0) 
	begin
		select @len = datalength(@param_name)
		select @padlen = @maxlen1 - @len
		select @param_name = @param_name + replicate(" ", @padlen)
		select @len = datalength(@strval1)
		select @padlen = @maxlen2 - @len
		select @strval1 = @strval1 + replicate(" ", @padlen)
		if (@strval1 is null) 
		begin
			select @padlen = @maxlen2 +4
		end
		else if (@strval2 is null)
		begin
			select @padlen = 7
		end
		else
			select @padlen = 1
		select @prdefstr = replicate("", @padlen) + @isdefstr

		print  " %1! %2! %3! %4!", 
			@param_name, @strval1, @strval2, @prdefstr

		fetch cursor_config_report into @param_name, @strval1,
				@strval2, @isdefstr
	end
	close cursor_config_report
	truncate table #config_report

	fetch cursor_dbcc_db_list into @dbid, @dbname
	select @sqlstatus1 = @@sqlstatus

	select @rtrn_status = 0
end

close cursor_dbcc_db_list

deallocate cursor cursor_dbcc_db_list
deallocate cursor cursor_config_report
drop table #dbcc_types
drop table #config_report
drop table #dbcc_db_list

set nocount off

if (@rtrn_status != 0)
begin
	/*
	** 18484, "No configuration information available for %1! database"
	*/
	raiserror 18484, @dbname
end
return (@rtrn_status)
go
grant execute on sp_dbcc_run_configreport to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_deletehistory')
begin
	drop procedure sp_dbcc_run_deletehistory
end
go
print "Installing sp_dbcc_run_deletehistory"
go

/*
** Messages for "sp_dbcc_run_deletehistory"	
**
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 17260, "Can't run %1! from within a transaction."
** 18472, "No history information to delete."
** 18473, "Deleting results of DBCC operations completed before %1! on database %2!."
*/
create procedure sp_dbcc_run_deletehistory 
( 
		@cutoffdate  datetime =  null,
		@dbname  varchar(255) = null
)
as
declare  @dbid smallint 		/* dbid of the database */
declare  @dbo int                     /* id of the owner of the database */
declare  @ret int                      
declare  @opid smallint			/* operation id */ 
declare  @finish datetime               /* finish time of dbcc operation */
declare  @last_finish  datetime		/* finish time of last operation */ 
declare  @msg varchar(1024) 
declare  @rtrn_status  int		/* set to 0 when we delete some data */
declare  @hour int, @minute  int 
declare  @date1 datetime
declare  @dummy int
declare  @sproc varchar(255) 

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        exec sp_getmessage 17260, @msg output
        raiserror 17260 @msg, "sp_dbcc_deletehistory"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

create table  #dbcc_db_list(dbid  smallint)
declare cursor_dbcc_dblist cursor  
	for select  dbid from #dbcc_db_list 


select @dbid = null
select @dbo = 0

/*
** If the dbname was specified by the user, validate it 
*/
if (@dbname is not null)
begin
	/* 
	** @dbname can be a number (dbid) or a database name.
	** If @dbname is a number, most likely the database corresponding
	** this dbid has been dropped since the last checkstorage operation. 
	** So do not validate the dbid passed against sysdatabases.
	** If @dbname represent database name, validate it against 
	** sysdatabases.
	*/
	if (@dbname like "[0-9]%") 	
	begin
		select @dbid = convert(smallint, @dbname)

		/*
		**  insert this dbid and name in #dbcc_db_list
		*/
		insert #dbcc_db_list(dbid) select @dbid
	end
	else 				/* @dbname is database name */
	begin
    		exec  @ret = sp_validatedb  @dbname, @dbid output
		/*
		** If the dbname is valid, delete everything from 
		** #dbcc_db_list because we are interested in only 
		** one database. Else return.
		*/
	    	if (@dbid is not null)
		begin
			exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, 
				@dbname
			insert #dbcc_db_list(dbid) 
				select distinct o.dbid
				from dbcc_operation_log o 
				where o.dbid = @dbid
		end
		else
			return (1)
	end
end
else
begin
	/*
	**  insert dbname and dbid for all valid databases in #dbcc_db_list
	*/
	insert #dbcc_db_list(dbid) 
		select distinct o.dbid
		from dbcc_operation_log o, master..sysdatabases d
		where d.dbid = o.dbid 
end

/*
**  Only the Database Owner (DBO) or
**  Accounts with SA role can execute it.
*/
if ((@dbo != 1) and charindex("sa_role", show_role()) < 1)
begin
	select @dummy = proc_role("sa_role")
	if (@dbname is null) 
		/*
		** 18990, "You must be the System Administrator (SA) 
		** to execute this procedure with a NULL database name 
		** (dbname) parameter.
		*/
		raiserror 18990
	else
		/*
		** 17230, "You must be the System Administrator (SA) 
		** or the Database Owner (dbo) to execute this 
		** procedure."
		*/
		raiserror 17230
	return (1)
end

create table  #dbcc_deletelist(dbid  smallint, opid  smallint)

/*
** 0 if we actually deleted any data. Set to 1 to start with
*/
select @rtrn_status = 1
/*
** for each database, prepare a list of opids to delete
*/
open cursor_dbcc_dblist
fetch cursor_dbcc_dblist into @dbid
while (@@sqlstatus = 0) 
begin
	if (@cutoffdate is null)
	begin
		/*
		** Do not delete the results of the last DBCC operation
		*/
		select @last_finish =  max(finish) 
			from dbcc_operation_log where dbid = @dbid
		select @last_finish = dateadd(minute, -1,  @last_finish)
	end
	else
	begin
		/*
		** If the hour and minute components of time
		** is missing, set time to <date>:23:59
		** 1440 is the number of minutes in a day. 
		*/
		select @hour = datepart(hour, @cutoffdate)
		select @minute = datepart(minute, @cutoffdate)
		if (@hour = 0 and @minute = 0)
		begin
			select @date1 = dateadd(minute, 1439, @cutoffdate)
			select @cutoffdate = @date1
		end
		select @last_finish =  max(finish) 
			from dbcc_operation_log 
			where dbid = @dbid and finish <= @cutoffdate
	end

	/*
	** If no rows qualify as having a finish time less than the
	** specified time, then it is possible that there are one or
	** more rows which should qualify, but do not because
	** DBCC CHECKSTORAGE terminated abnormally (eg due to 
	** control C), so that the finish time is specified as NULL.
	** In such cases, use the start time for the run to determine
	** qualifying aborted runs.
	*/
	if ((@last_finish is null) and (@cutoffdate is not null))
	begin
		select @last_finish = max(start) from dbcc_operation_log
		where dbid = @dbid and start <= @cutoffdate
	end

	/*
	** @last_finish has the finish time of the last dbcc operation
	** If null, continue with next database in the list
	*/
	if (@last_finish is null)
	begin
		fetch cursor_dbcc_dblist into @dbid
		continue
	end

	/*
	**  Get the opid and finish time of dbcc operation completed
	**  before @last_finish on the databases listed in #dbcc_db_list
	*/
	insert #dbcc_deletelist(dbid, opid)
		select distinct o.dbid, o.opid
		from dbcc_operation_log o 
		where o.dbid = @dbid and o.finish <= @last_finish

	/*
	** Abnormally terminated runs must be included in the delete list
	** by looking for runs with a NULL finish date whose start time is
	** less than or equal to the cut off time.
	*/
	insert #dbcc_deletelist(dbid, opid)
		select distinct o.dbid, o.opid from dbcc_operation_log o
		where o.dbid = @dbid and o.finish is null and o.start <= @last_finish

	if exists (select * from #dbcc_deletelist)
	begin
		select @dbname = db_name(@dbid)
		/*
		** 18473, "Deleting results of DBCC operations completed before %1! on database %2!"
		*/
		exec sp_getmessage 18473, @msg output
        	print @msg, @last_finish, @dbname
		delete dbcc_operation_results 
			from dbcc_operation_results o, #dbcc_deletelist dl
			where o.dbid = @dbid and o.opid = dl.opid 
		
		delete dbcc_faults 
			from dbcc_faults f, #dbcc_deletelist dl 
			where f.dbid = @dbid and f.opid = dl.opid 
		
		delete dbcc_fault_params 
			from dbcc_fault_params fp, #dbcc_deletelist dl 
			where fp.dbid = @dbid and fp.opid = dl.opid 
		
		delete dbcc_counters 
			from dbcc_counters c, #dbcc_deletelist dl
			where c.dbid = @dbid and c.opid = dl.opid 
	
		delete dbcc_operation_log 
			from dbcc_operation_log ol, #dbcc_deletelist dl
			where ol.dbid = @dbid and ol.opid = dl.opid 
		select @rtrn_status = 0
	end
	truncate table #dbcc_deletelist
	fetch cursor_dbcc_dblist into @dbid
end

if (@rtrn_status != 0)
begin
	/*
	** 18472, "No history information to delete."
	*/
	exec sp_getmessage 18472, @msg output
	print @msg
end
return (@rtrn_status)
go
grant execute on sp_dbcc_run_deletehistory to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_deletedb')
begin
	drop procedure sp_dbcc_run_deletedb
end
go
print "Installing sp_dbcc_run_deletedb"
go


/*
** Delete all information on the specified database from dbccdb
** If @dbname is null delete all databases from dbccdb
*/
/*
** Messages for "sp_dbcc_run_deletedb"	
**
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 17260, "Can't run %1! from within a transaction."
** 18470, "No configuration information to delete."
** 18471, "Deleting Configuration information of database %1!."
** 18990, "You must be the System Administrator (SA) to execute this procedure with a NULL database name (dbname) parameter.
*/
create procedure sp_dbcc_run_deletedb 
( 
		@dbname  varchar(255) = null
)
as
declare  @dbid smallint 		/* dbid of the database */
declare  @dbo int                     /* id of the owner of the database */
declare  @curdate datetime              /* today's time */ 
declare  @msg varchar(1024) 		/* buffer for error messages */
declare  @ret int			/* a place to store return value */
declare  @ret_status int		/* a place to store status value */
declare  @dummy int
declare  @dbname_isnull int
declare  @is_remote_tdb int

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        exec sp_getmessage 17260, @msg output
        raiserror 17260 @msg, "sp_dbcc_deletedb"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

/*
** Temporary table to store name and id of all databases to 
** be deleted from dbccdb database
*/
create table  #dbcc_db_list(dbid  smallint, dbname  varchar(255))
declare cursor_dbcc_db_list cursor  
	for select  dbid, dbname from #dbcc_db_list 

select @dbid = null
select @dbo = 0

if (@dbname is not null)
begin
	select @dbname_isnull = 0

	/* 
	** @dbname can be a number (dbid) or a database name.
	** If @dbname is a number, most likely the database corresponding
	** this dbid has been dropped since the last checkstorage operation. 
	** So do not validate the dbid passed against sysdatabases.
	** If @dbname represent database name, validate it against 
	** sysdatabases.
	*/
	if (@dbname like "[0-9]%") 	
	begin
		select @dbid = convert(smallint, @dbname)

		/*
		**  insert this dbid and name in #dbcc_db_list
		*/
		insert #dbcc_db_list(dbid, dbname) 
			select @dbid, convert(varchar, @dbid)
	end
	else 				/* @dbname is database name */
	begin
    		exec  @ret = sp_validatedb  @dbname, @dbid output
		/*
		** If the dbname is valid, delete everything from 
		** #dbcc_db_list because we are interested in only 
		** one database. Else return.
		*/
    		if (@dbid is not null)
		begin
			exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, 
				@dbname
			insert #dbcc_db_list(dbid, dbname)
				select distinct c.dbid, @dbname
				from dbcc_config c
				where c.dbid = @dbid
		end
		else
			return (1)
	end
end
else
begin
	select @dbname_isnull = 1

	/*
	**  insert dbname and dbid for all databases in #dbcc_db_list
	*/
	insert #dbcc_db_list(dbid, dbname) 
		select distinct c.dbid, d.name 
		from dbcc_config c, master..sysdatabases d
		where d.dbid = c.dbid 
end


/*
**  Only the Database Owner (DBO) or
**  Accounts with SA role can execute it.
*/
if ((@dbo != 1) and charindex("sa_role", show_role()) < 1)
begin
	select @dummy = proc_role("sa_role")
	if (@dbname is null) 
		/*
		** 18990, "You must be the System Administrator (SA) 
		** to execute this procedure with a NULL database name 
		** (dbname) parameter.
		*/
		raiserror 18990
	else
                /*
                ** 17230, "You must be the System Administrator (SA) 
                ** or the Database Owner (dbo) to execute this 
		** procedure."
                */
                raiserror 17230
	return (1)
end

select @ret_status = 1

/*
** for each database, delete the entire DBCC history and 
** configuration information.
*/
select @curdate = getdate()
open cursor_dbcc_db_list
fetch cursor_dbcc_db_list into @dbid, @dbname
while (@@sqlstatus = 0) 
begin

	/*
	** 18471, "Deleting Configuration information of database %1!"
	*/
	exec sp_getmessage 18471, @msg output
	print @msg, @dbname
	
	exec sp_dbcc_run_deletehistory  @curdate, @dbname

	delete from dbcc_config where dbid = @dbid

	select @ret_status = 0

	fetch cursor_dbcc_db_list into @dbid, @dbname
end

close cursor_dbcc_db_list
deallocate cursor cursor_dbcc_db_list
drop table #dbcc_db_list

if (@ret_status != 0)
begin
	/*
	** 18470, "No configuration information to delete."
	*/
	raiserror 18470
end

return (@ret_status)
go
grant execute on sp_dbcc_run_deletedb to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_evaluatedb')
begin
	drop procedure sp_dbcc_run_evaluatedb
end
go
print "Installing sp_dbcc_run_evaluatedb"
go


/*
** This procedure recalculates the configuration parameters using
** the results of the previous checkstorage operation. The configuration
** parameters reported by sp_dbcc_run_evaluatedb are expected to be more
** accurate than sp_dbcc_plandb.
*/

/*
** Messages for "sp_dbcc_run_evaluatedb"
**
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 17260, "Can't run %1! from within a transaction."
** 18458, "Recommended values for workspace size, cache size and worker process count are:"
** 18474, "%1! database is not configured for DBCC in the dbcc_config table."
** 18482, "Since dbccalt exists, it is the correct data base for dbccdb. Use the dbccalt database."
*/
create procedure sp_dbcc_run_evaluatedb (
		@dbname  varchar(255) = null)
as
declare @dbid int
declare @dbo int
declare @dbcc_opid int
declare @dbsize int
declare	@cur_scanws_size int, @new_scanws_size int
declare	@cur_textws_size int, @new_textws_size int
declare	@cur_cache_size int, @new_cache_size int
declare	@cur_comp_size int, @new_comp_size int
declare	@cur_wt_count smallint, @new_wt_count smallint
declare	@min_cachesize int
declare	@dev_count int
declare @objid int, @doampg int
declare @extents_used int
declare @classid smallint
declare @classcount int
declare @devid int
declare @text_colcount int
declare @textcols_per_page int
declare @sqlstatus1 int
declare @ret int
declare @title_printed int
declare @KB_per_page int
declare @min_wssize int		/* minimum size of workspace */
declare @new_min_wssize int	/* revised minimum size of workspace */
declare @cur_cache_sf int	/* current cache size in KB */
declare @cur_cache_du char(1)	/* cache display unit */
declare @cur_scanws_sf int	/* scan ws size in KB */
declare @cur_scanws_du char(1)	/* scan ws display unit */
declare @cur_textws_sf int	/* text ws size in KB */
declare @cur_textws_du char(1)	/* text ws display unit */
declare @cur_comp_sf int	/* compression pool size in KB */
declare @cur_comp_du char(1)	/* compression pool size display unit */
declare @new_cache_sf int	/* new cache size in KB */
declare @new_cache_du char(1)	/* cache display unit */
declare @new_scanws_sf int	/* scan ws size in KB */
declare @new_scanws_du char(1)	/* scan ws display unit */
declare @new_textws_sf int	/* text ws size in KB */
declare @new_textws_du char(1)	/* text ws display unit */
declare @new_comp_sf int	/* compression pool size in KB */
declare @new_comp_du char(1)	/* compression pool size display unit */
declare @report_string varchar(80)   /* used to print outputs */
declare @scratchdb sysname	/* scratch database */
declare @qrystr varchar(312)	/* dynamic query */
declare @adb_stat int		/* archive database status */
declare @comp_stat int		/* compression status */
declare @compdata_stat int	/* data compression status */
declare @complog_stat int	/* log compression status */
declare @status3 int		/* status3 from sysdatabases */
declare @adb_dbid int		/* archive database id */
declare @name varchar(255), @wsname varchar(255),
	@devname varchar(255)
declare  @ws_dbid	int		/* workspace dbid */

/*
** These variables are used for formatting the output
*/
declare @col1 varchar(22), @col2 varchar(22)
declare @msg varchar(1024)
declare @dummy int

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if (@@trancount > 0)
begin
	/*
	** 17260, "Can't run %1! from within a transaction."
	*/
	exec sp_getmessage 17260, @msg output
	raiserror 17260 @msg, "sp_dbcc_evaluatedb"
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1
set nocount on

select @adb_stat=number from master.dbo.spt_values
where name="archive database" and type="D3"

select @compdata_stat=number from master.dbo.spt_values
where name="compressed data" and type="D3"

select @complog_stat=number from master.dbo.spt_values
where name="compressed log" and type="D3"

select @comp_stat = @compdata_stat | @complog_stat

/*
** create tables and storing temporary results and declare
** cursors on them
*/

create table #dbcc_dblist(dbid smallint, dbname varchar(30), 
				status3 int)
declare cursor_dbcc_dblist cursor for
	select dbid, dbname, status3 from #dbcc_dblist 

create table #dev_class(dbid int, classid smallint null)

create table #temp_config(dbid int, dbname varchar(30) null, 
	cur_scanws_size int null, new_scanws_size int null,
	cur_textws_size int null, new_textws_size int null,
	cur_wt_count int null, new_wt_count int null,
	cur_cache_size int null, new_cache_size int null,
	cur_comp_size int null, new_comp_size int null)

create table #dbcc_dev(dbid int, ldev_name varchar(30))

declare cursor_dbcc_dev cursor for
	select ldev_name from #dbcc_dev where dbid = @dbid

/*
** insert dbname and dbid for all valid databases in #dbcc_dblist
*/
insert #dbcc_dblist(dbid, dbname, status3)
	select d.dbid, d.name, d.status3
	from master..sysdatabases d
	where exists ( select * from dbcc_config c
				where c.dbid = d.dbid )


select @dbo = 0
if (@dbname is not null)
begin
	if ((@dbname = 'dbccdb') and (db_id() != db_id('dbccalt')))
	begin
	 	/*
        	** 18482, "Since dbccalt exists, it is the correct data base for dbccdb. Use the dbccalt database."
        	*/
		raiserror 18482
		return(1)
	end
	/*
	** If the dbname is valid, delete other databases from
	** #dbcc_dblist. Otherwise return error
	** because we are interested in only one database
	*/
	select @dbid = db_id(@dbname)
  	if (@dbid is not null)
	begin
		delete from #dbcc_dblist where dbid != @dbid
		exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname
	end
	else
	begin
		/*
		** 17421, "No such database -- run sp_helpdb to list databases."
		*/
		raiserror 17421
		return (1)
	end
end

/*
** Only the Database Owner (DBO) or
** Accounts with SA role can execute it.
*/
if ((@dbo != 1) and charindex("sa_role", show_role()) < 1)
begin
	select @dummy = proc_role("sa_role")
	if (@dbname is null)
		/*
		** 18990, "You must be the System Administrator (SA) 
		** to execute this procedure with a NULL database name 
		** (dbname) parameter.
		*/
		raiserror 18990
	else
		/*
		** 17230, "You must be the System Administrator (SA) 
		** or the Database Owner (dbo) to execute this 
		** procedure."
		*/
		raiserror 17230
		return (1)
end

if ((select count(*) from #dbcc_dblist) < 1)
begin
	/*
	** 18474, "%1! database is not configured for DBCC in the dbcc_config table"
	*/
	raiserror 18474, @dbname
	return(1)
end

/*
** Get the number of KB per sybase page
*/
select @KB_per_page = @@maxpagesize / 1024

/*
** Set the absolute minimum size required for workspaces
*/
select @min_wssize = 24 * @KB_per_page

/*
** The workspace dbid will correspond to either dbccdb or dbccalt
*/
select @ws_dbid = db_id()

/*
** Get the compression memory pool size. This memory is only needed
** to access a compressed archive database. In most cases, when the
** database was created using a block size of 64KB, it is enough
** with 128KB. But the block size can grow up to 2MB. In this case
** we need twice that value, 4MB per thread. This will be set as the 
** recommended value because we cannot easily know from SQL the block 
** size used. This value is stored in 2KB memory pages. It is independent 
** of the server page size.
*/
select @cur_comp_size=value * 2 from master.dbo.sysconfigures
where config=455 and name="compression memory size"

/*
** Get the list of devices used by each database in 
** master..sysdatabase
*/
insert #dbcc_dev(dbid, ldev_name)
	select distinct db.dbid, dev.name
	from master..sysdatabases db, master..sysusages u, 
		master..sysdevices dev
	group by db.dbid
	having
		db.dbid  = u.dbid and
		u.vdevno = dev.vdevno and
		dev.cntrltype = 0 and
		db.status3 & @adb_stat = 0
/*
** Add dbid and devname for all the archive databases.
*/
declare cursor_adb cursor for
	select dbid from master.dbo.sysdatabases
	where (status3 & @adb_stat) = @adb_stat

open cursor_adb
fetch cursor_adb into @adb_dbid
while (@@sqlstatus = 0)
begin
	/*
	** Read the scratch database name from sysattributes.
	*/
	select @scratchdb = convert(sysname, char_value)
	from master.dbo.sysattributes
	where  class=28
		and object_type="D"
		and object=@adb_dbid
		and attribute=0

	if (db_id(@scratchdb) is not null)
	begin
		select @qrystr = 'insert #dbcc_dev(dbid, ldev_name) ' +
			'select distinct @adb_dbid, dev.name ' +
			'from master..sysdevices dev, '+
				@scratchdb + '.dbo.sysaltusages sa '+
			'where sa.dbid=@adb_dbid and ' +
				'sa.location = 5 and ' +
				'dev.status & 4 = 4 and ' +
				'sa.vdevno = dev.vdevno '
		exec (@qrystr)
	end
	fetch cursor_adb into @adb_dbid
end
close cursor_adb
deallocate cursor cursor_adb

/*
** For each dbid in #dbcc_dblist, calculate the configuration parameters
*/
open cursor_dbcc_dblist
fetch cursor_dbcc_dblist into @dbid, @name, @status3
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0) 
begin

	/*
	** If the named database is not in the dbcc_config table 
	** print a message and continue
	*/
	if not exists (select * from dbcc_config where dbid = @dbid)
	begin
		/*
		** 18474, "%1! database is not configured for DBCC in the dbcc_config table"
		*/
		raiserror 18474, @name
		fetch cursor_dbcc_dblist into @dbid, @name, @status3
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	/* initialize the current values to 0 */
	select @cur_wt_count = 0
	select @cur_cache_size = 0
	select @cur_scanws_size = 0
	select @cur_textws_size = 0
	select @new_comp_size = 0

	select @cur_wt_count = value from dbcc_config
		where dbid = @dbid and type_code = 1
	if (@cur_wt_count is null)
		select @cur_wt_count = 0

	select @cur_cache_size = value from dbcc_config
		where dbid = @dbid and type_code = 2
	if (@cur_cache_size is null)
		select @cur_cache_size = 0

	/* Get the name and size (in number of pages) of scan workspace */
	select @wsname = stringvalue from dbcc_config 
		where dbid = @dbid and type_code = 3

	/* See note tagged by NOTE_ABOUT_BUILT_INS in file 
	** sproc/dbcc_run_configreport
	*/
	select @cur_scanws_size = (case 
				     when indid <= 1
				     then data_pages(@ws_dbid, id, 
							case
							   when indid = 1
							   then 0
							   else indid
						        end)
				     else 0
				   end)
		from sysindexes where name = @wsname
	if (@cur_scanws_size is null)
	begin
		select @cur_scanws_size = 0
	end
	/*
	** First, convert scanws size to KB. Then covert into string
	*/
	select @cur_scanws_size = @cur_scanws_size * @KB_per_page

	/*
	** Get the name and size (in number of pages) of text workspace
	*/
	select @wsname = stringvalue from dbcc_config 
		where dbid = @dbid and type_code = 4

	/* See note tagged by NOTE_ABOUT_BUILT_INS in file 
	** sproc/dbcc_run_configreport
	*/
	select @cur_textws_size = (case 
				     when indid <= 1
				     then data_pages(@ws_dbid, id,
							case
							   when indid = 1
							   then 0
							   else indid
							end)
				     else 0
				   end)
		from sysindexes where name = @wsname
	if (@cur_textws_size is null)
	begin
		select @cur_textws_size = 0
	end
	/*
	** convert textws size to KB
	*/
	select @cur_textws_size = @cur_textws_size * @KB_per_page

	insert #temp_config (dbid, dbname, cur_scanws_size, new_scanws_size, 
		cur_textws_size, new_textws_size, cur_wt_count, new_wt_count, 
		cur_cache_size, new_cache_size, 
		cur_comp_size, new_comp_size)
		values (@dbid, @name, 
			@cur_scanws_size, @cur_scanws_size, 
			@cur_textws_size, @cur_textws_size, 
			@cur_wt_count, @cur_wt_count, 
			@cur_cache_size, @cur_cache_size, 
			@cur_comp_size, @cur_comp_size)

	/*
	** Find out the number of I/O classes from dbcc_dev_info table.
	** This will decide the number of worker processes to use.
	*/
	open cursor_dbcc_dev
	fetch cursor_dbcc_dev into @devname
	while (@@sqlstatus = 0) 
	begin
		select @devid = vdevno
			from master..sysdevices where name = @devname
		select @classid = classid from dbcc_dev_info
			where devid = @devid
		if not exists (select * from #dev_class 
			where dbid = @dbid and classid = @classid)
		begin
			insert #dev_class (dbid, classid) 
				values(@dbid, @classid)
		end
		fetch cursor_dbcc_dev into @devname
	end
	close cursor_dbcc_dev

	select @classcount = count(*) from #dev_class 
			where dbid = @dbid and classid is not null

	if (@classcount < 10)
	begin
		select @new_wt_count = @classcount
	end
	else 
	begin
		if (@classcount > 50)
		begin
			select @new_wt_count = 30 + (@classcount - 50) / 8
		end
		else
		begin
			select @new_wt_count = 10 + (@classcount - 10) / 2
		end
	end

	/*
	** @new_wt_count is expected to be at least 1. Otherwise
	** set it the number of devices this database belongs to
	*/
	if (@new_wt_count < 1)
	begin
		select @new_wt_count = count(*) from #dbcc_dev 
				where dbid = @dbid
	end

	/*
	** The minimum workspace size depends on the number of worker
	** processes too. So adjust the value.
	*/
	select @new_min_wssize = ((@new_wt_count + 1) * 8 * @KB_per_page)

	if (@new_min_wssize < @min_wssize)
		select @new_min_wssize = @min_wssize

	/*
	** Get the size of this database (unit : number of pages)
	*/
	if ((@status3 & @adb_stat) != 0)
	begin
		/*
		** This is an archive database. The original diskmap is 
		** stored in the sysaltusages catalog in the scratch 
		** database with a location = 4.
		** Read the scratch database name from sysattributes first.
		*/
		select @scratchdb = convert(sysname, char_value)
			from master.dbo.sysattributes
		where class=28
			and object_type="D"
			and object=@dbid
			and attribute=0

		select @qrystr =
			'select @dbsize=sum(size) from ' +
				@scratchdb + '.dbo.sysaltusages '+
			'where dbid=@dbid and location = 4'
		exec (@qrystr)

		/*
		** Set recommended compression pool size to 4MB per thread
		** if database is compressed.
		*/
		if ((@status3 & @comp_stat) != 0)
		begin
			select @new_comp_size = 4096 * @new_wt_count
		end
	end
	else
	begin
		select @dbsize = sum (size) 
		from master..sysusages 
		where dbid = @dbid
	end

	/*
	** Calculate the new size for scanws 
	** It is 1.2% of @dbsize.
	*/
	select @new_scanws_size = ceiling(@dbsize * 0.012)

	/*
	** set the size to the next multiple of 8 which is the unit
	** of allocation
	** This calculation ensure new_scanws_size is NEXT multiple
	** even when it would fit into an allocation unit.
	*/
	select @new_scanws_size = ((@new_scanws_size / 8) + 1) * 8 
	/*
	** convert scanws size to KB
	*/
	select @new_scanws_size = @new_scanws_size * @KB_per_page
	/*
	** Minimum scan workspace size is @new_min_wssize KB 
	*/
	if (@new_scanws_size < @new_min_wssize)
		select @new_scanws_size = @new_min_wssize
	/*
	** For small databases (size < 20MB), increase the size of 
	** scanws by 8 pages. This is because checkstorage uses one 
	** full extent (8 pages) for page mapping. As a result 1.2%
	** of database size may not be enough for scanws if database is
	** small.
	*/
	if (@new_scanws_size <= 256)
		select @new_scanws_size = @new_scanws_size + (8 * @KB_per_page)

	select @dbcc_opid = value from dbcc_config
        where dbid = @dbid and type_code = 5

	/*
	** 1007 is the type_code for text column count
	*/
	select @text_colcount = sum(intvalue)
	from dbcc_operation_results
	where dbid = @dbid and opid = @dbcc_opid and type_code = 1007

	/*
	** There are 39000 text entries per MB. So
	** (39000 * @@pagesize)/(1024 * 1024) will give number of
	** entries per SYBASEPAGE.
	*/
	select @textcols_per_page = 
			(39000 * @@pagesize)/(1024 * 1024)

	/*
	** determine the number of text workspace pages required
	** 
	*/
	if (@text_colcount > @textcols_per_page)
	begin
		select @new_textws_size = 
			ceiling(1.0 * @text_colcount / @textcols_per_page)
		/*
		** Make it a multiple of 8
		*/
		select @new_textws_size = (ceiling(@new_textws_size / 8.0)) * 8
		/*
		** convert textws size to KB
		*/
		select @new_textws_size = 
			@new_textws_size * @KB_per_page
	end
	else
	begin
		/*
		** scanws_size / 4.0 is rough estimate used in
		** sp_plan_dbccdb
		*/
		if (@cur_textws_size > @new_scanws_size)
			select @new_textws_size = 
				ceiling(@new_scanws_size / 4.0)
		else
			select @new_textws_size = @cur_textws_size
	end
	if ((@new_textws_size is null) or (@new_textws_size < @new_min_wssize))
		select @new_textws_size = @new_min_wssize

	/*
	** The sum of value for "extents used" by all objects except
	** SYSALLOCPG in dbcc_counters will give total extents used.
	** type_code 5005 is for number of extents used by an objects
	*/
	select @extents_used = convert (int, sum(value)) from dbcc_counters
	where dbid = @dbid and opid = @dbcc_opid and id != 99
			and type_code = 5005 

	/*
	** One 16K buffer covers approximately 90 extents. 
	** So new_cache_size in KB will be (@extents_used/90) * 16
	*/
	select @new_cache_size = ceiling(@extents_used/90.0) * 16 

	/*
	** Adjust the size of workspaces and cache based on the number
	** of worker processes. 
	** The workspaces must be at least (@new_wt_count + 1) * 8 pages.
	** There must be atleast 640KB of memory for 16K or 64K buffer pools
	** for each worker process. set @min_cachesize to this value in KB
	*/

	select @min_cachesize = 640 * @new_wt_count

	if ((@new_cache_size is null) or (@new_cache_size < @min_cachesize))
	begin
		select @new_cache_size = @min_cachesize
	end

	/*
	** Insert the values calculated for this database into
	** #dbcc_config table. This table is used for report generation
	*/
	update #temp_config set 
			new_scanws_size = @new_scanws_size, 
			new_textws_size = @new_textws_size, 
			new_cache_size = @new_cache_size,
			new_wt_count = @new_wt_count,
			new_comp_size = @new_comp_size
	where dbid = @dbid

	fetch cursor_dbcc_dblist into @dbid, @name, @status3
	select @sqlstatus1 = @@sqlstatus

end
close cursor_dbcc_dblist

/*
** This should not happen, but check anyway. If #temp_config 
** empty don't proceed further
*/
if not exists (select * from #temp_config)
begin
	return(1)
end

/*
** print the title first time when the loop is entered.
*/
select @title_printed = 0

declare cursor_temp_config cursor for
	select dbname, cur_scanws_size, new_scanws_size, 
	 	  cur_textws_size, new_textws_size, cur_wt_count, 
		  new_wt_count, cur_cache_size, new_cache_size,
		  cur_comp_size, new_comp_size
	from #temp_config 


open cursor_temp_config 

fetch cursor_temp_config 
	into @name, @cur_scanws_size, @new_scanws_size, 
	@cur_textws_size, @new_textws_size, @cur_wt_count, 
	@new_wt_count, @cur_cache_size, @new_cache_size,
	@cur_comp_size, @new_comp_size

while (@@sqlstatus = 0) 
begin
	if (@title_printed = 0)
	begin
		/*
		** 18458, "Recommended values for workspace size, cache size and worker process count are:"
		*/
		exec sp_getmessage 18458, @msg output
		print @msg
		print ""
		select @title_printed = 1
	end
	
	/*
	** sizes are in KB. Find out the appropriate unit to use for
	** displaying these values.
	** NOTE: first parameter is currently an int. Thus we
	** cannot specify a size (in KB) greater then 2 billion k
	** or 2 TB. If this becomes necessary, change first parameter
	** to numeric and modify this proc and sp_dbcc_scale_factor
	** accordingly.
	** We also note that customers can have hugely overmatched
	** current versus suggested ws, so we allow each unit to
	** be different
	*/

	execute sp_dbcc_scale_factor @new_cache_size,
		@scale_factor = @new_cache_sf output,
		@scale_letter = @new_cache_du output

	execute sp_dbcc_scale_factor @new_textws_size,
		@scale_factor = @new_textws_sf output,
		@scale_letter = @new_textws_du output

	execute sp_dbcc_scale_factor @new_scanws_size,
		@scale_factor = @new_scanws_sf output,
		@scale_letter = @new_scanws_du output

	execute sp_dbcc_scale_factor @new_comp_size,
		@scale_factor = @new_comp_sf output,
		@scale_letter = @new_comp_du output

	execute sp_dbcc_scale_factor @cur_cache_size,
		@scale_factor = @cur_cache_sf output,
		@scale_letter = @cur_cache_du output

	execute sp_dbcc_scale_factor @cur_textws_size,
		@scale_factor = @cur_textws_sf output,
		@scale_letter = @cur_textws_du output

	execute sp_dbcc_scale_factor @cur_scanws_size,
		@scale_factor = @cur_scanws_sf output,
		@scale_letter = @cur_scanws_du output

	execute sp_dbcc_scale_factor @cur_comp_size,
		@scale_factor = @cur_comp_sf output,
		@scale_letter = @cur_comp_du output

	/*
	** Produce summary report.
	*/

	print "Database name : %1!", @name

	/*
	** Format output as a table for comparitive purposes.
	** We assume from sp_dbcc_scale_factor that the
	** max field with is 8, but we will round this to 15
	** to give a good separation between the columns.
	** Our messages will be a fixed width (including :)
	** of 21 + 8 space chars followed by our two fields of 16
	** separated by two spaces.
	**
	** Each number field cannot be negative (so no sign required)
	** but has space for 15 integer digits and one unit character.
	*/

	select @report_string = space(21) + space(8) + space(9) +
		"current" + space(2) + space(7) + "suggested"

	print "%1!", @report_string

	/* scanws */

	select @col1 = str(ceiling(1.0 * @cur_scanws_size / @cur_scanws_sf), 15)
		+ @cur_scanws_du
	select @col2 =
		 str(ceiling(1.0 * @new_scanws_size / @new_scanws_sf), 15)
		+ @new_scanws_du
	select @report_string = "scan workspace size :" + space(8)
		+ @col1 + space(2) + @col2
	print "%1!", @report_string

	/* textws */

	select @col1 = str(ceiling(1.0 * @cur_textws_size / @cur_textws_sf), 15)
		+ @cur_textws_du
	select @col2 = str(ceiling(1.0 * @new_textws_size / @new_textws_sf), 15)
		+ @new_textws_du
	select @report_string = "text workspace size :" + space(8)
		+ @col1 + space(2) + @col2
	print "%1!", @report_string

	/* cache */

	select @col1 = str(ceiling(1.0 * @cur_cache_size / @cur_cache_sf), 15)
		+ @cur_cache_du
	select @col2 = str(ceiling(1.0 * @new_cache_size / @new_cache_sf), 15)
		+ @new_cache_du
	select @report_string = "cache size          :" + space(8)
		+ @col1 + space(2) + @col2
	print "%1!", @report_string

	/* worker thread count */

	select @col1 = str(@cur_wt_count, 16)
	select @col2 = str(@new_wt_count, 16)
	select @report_string = "process count       :" + space(8)
		+ @col1 + space(2) + @col2
	print "%1!", @report_string

	/* compression memory pool size. Print only if needed. */

	if (@new_comp_size > 0)
	begin
		select @col1 = str(ceiling(1.0 * @cur_comp_size / @cur_comp_sf), 15)
			+ @cur_comp_du
		select @col2 =
		 	str(ceiling(1.0 * @new_comp_size / @new_comp_sf), 15)
				+ @new_comp_du
		select @report_string = "compression mem size:" + space(8)
			+ @col1 + space(2) + @col2
		print "%1!", @report_string
	end
	print ""

	fetch cursor_temp_config 
		into @name, @cur_scanws_size, @new_scanws_size, 
		@cur_textws_size, @new_textws_size, @cur_wt_count,
		@new_wt_count, @cur_cache_size, @new_cache_size,
		@cur_comp_size, @new_comp_size
end
print ""

close cursor_temp_config

/*
** Clean-up all temporary tables
*/
deallocate cursor cursor_dbcc_dblist
deallocate cursor cursor_temp_config

drop table #dbcc_dblist
drop table #dev_class
drop table #temp_config

return(0)
go
grant execute on sp_dbcc_run_evaluatedb to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_updateconfig')
begin
	drop procedure sp_dbcc_run_updateconfig
end
go
print "Installing sp_dbcc_run_updateconfig"
go

/*
** Stored procedure "sp_dbcc_updateconfig" updates the dbccdb/dbccdbalt
** configuration settings that are used by dbcc checkstorage when checking
** database @dbname (or, if the @dbname parameter is null, it updates
** the default settings used for all databases that do not have a
** corresponding configuration value.
*/
/*
** Messages for "sp_dbcc_run_updateconfig" 	  
**
** 17421, "No such database -- run sp_helpdb to list databases."
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 18475, "Workspace %1! does not exist in database %2!."
** 18476, "'%1!' is not a valid value for '%2!'."
** 18477, "%1! is not a valid cache name."
** 18478, "The specified size for cache %1! is not valid."
** 18481, "'%1!' is an invalid configuration parameter."
** 18635, "The database '%1!' with id %2! was configured as '%3!' in dbccdb database. Please delete the configuration and history using the command '%4!' and retry the sp_dbcc_updateconfig command."
** 18990, "You must be the System Administrator (SA) to execute this procedure with a NULL database name (dbname) parameter."
** 18991, "Illegal value for 'max worker processes' encountered. Choose a value between 1 and 128 inclusive."
** 18992, "The default value of the dbcc configuration parameter '%1!' has been updated."
** 18993, "The value of the dbcc configuration parameter '%1!' for database %2! has been updated."
** 18994, "The dbcc configuration of '%1!' for database %2! does not exist.  Your request to delete the corresponding configuration value has been ignored."
** 19001, "The default value of the dbcc configuration parameter '%1!' has been deleted." 
** 19002, "The dbcc configuration of '%1!' for database %2! has been deleted."
** 19003, "dbcc configuration parameter '%1!' does not have a default value.  
**	   The delete request has been ignored."
** 19027, "Aborted %1! operation due to error '%2!'."
** 19028, "Ignoring redundant parameter ('%1!' = '%2!')."
** 19858, "The workspace %1! is already configured as %2!. Use sp_dbcc_configreport to display the current configuration and sp_dbcc_updateconfig to correct the problem."
** 19823, "The cache %1! cannot be used because it is an instance only cache."
**
*/

create procedure sp_dbcc_run_updateconfig 
( 
		@dbname  varchar(255) = NULL,
		@type    varchar(255),
		@str1    varchar(255),
		@str2    varchar(255) = NULL
)
as
declare @dbid int, 			/* dbid of the database */
	@thisdbname varchar(255),
	@type_code int,
	@type_code1 int,
	@name varchar(30),
	@wsname varchar(30),
	@stringvalue varchar(255),
	@value int,
	@ret int,
	@dbo int,                      	/* is the owner of the database ? */
	@error_num varchar(30),
	@msg varchar(1024),
	@dbname_is_null int,            /* TRUE if dbname is NULL */
	@delete_config_val int,		/* TRUE if user wants to delete the
					** configuration value */
	@is_archivedb int,
	@scratchdb sysname,
	@qrystr varchar(256),
	@adbstat int,
	@dummy int


select @delete_config_val = 0

if @@trancount = 0
begin
        set chained off
end
 
set transaction isolation level 1

set nocount on

select @adbstat=number from master.dbo.spt_values
where name="archive database"

if ((@dbname is NULL) or (lower(@dbname) = 'null'))
begin
        select @dbname_is_null = 1
        select @dbid = 0
        select @dbname = 'DEFAULT_VALUES'
	select @dbo = 0
	select @is_archivedb = 0
end
else
begin
        /* Verify the database name (if given) and get the @dbid and @dbuid */
        select @dbname_is_null = 0
        select @dbid = dbid, @is_archivedb = status3 & @adbstat
                from master.dbo.sysdatabases
                where name = @dbname
end

/*
**  If user supplied @dbname not found, say so and list the databases.
*/
if @dbid is NULL
begin
	/*
	** 17421, "No such database -- run sp_helpdb to list databases."
	*/
	raiserror 17421
	return (1)
end

/* 
** Make sure the @str1 parameter is not null (as it is required).
** If its value is 'delete' the user wants to delete the value of the
** configuration parameter given in the @type parameter.
*/
if (@str1 is not null)
begin
	if (lower(@str1) = 'delete') 
	begin
		select @delete_config_val = 1
		if (@str2 is not null)
		begin
			/*
			** "19028, Ignoring redundant parameter
			**  ('%1!' = '%2!')."
			*/
			exec sp_getmessage 19028, @msg output
			print @msg, '@str2', @str2
		end
	end 

end
else
begin
	/* 18476, "'%1!' is not a valid value for '%2!'." */
	raiserror 18476, 'Null', 'the @str1 parameter'
	return (1)
end

select @type_code = type_code from dbcc_types where
		type_name = @type
/*
** Valid type_code values for configuration parameters range between 1-12
** User specifies only type_codes 1, 2, 3, 4, 7, 8, 9, 10, 11 and 12.
** 5 and 6 are set when other types are set.
*/
if ((@type_code is null) or (@type_code < 1) or (@type_code > 12) or 
	 (@type_code = 5) or (@type_code = 6))
begin
	if (@type is null)
		select @name = 'null'
	else
		select @name = @type

	/*
	** 18481, "'%1!' is an invalid configuration parameter."
	*/
	raiserror 18481, @name
	return (1)
end

/* 
** If the passed in database was null, it will apply to
** all the databases, and this requires sa_role.
*/
if (@dbname_is_null = 0)
begin
	exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname
end

/*
** The user must be the Database Owner (DBO) or
** have the SA role to execute this sproc.
*/
if ((@dbo != 1) and charindex("sa_role", show_role()) < 1)
begin
	/*
	** Call proc_role() to generate the appropriate
	** audit record.
	*/
	select @dummy = proc_role("sa_role")
	if (@dbname_is_null = 1)
        	/*
        	**  18990, "You must be the System Administrator (SA) to execute this
        	**  procedure with a NULL database name (dbname) parameter."
        	*/
        	raiserror 18990
	else
		/*
		** 17230, "You must be the System Administrator (SA) or
		** the Database Owner (dbo) to execute this procedure."
		*/
		raiserror 17230
	return (1)
end

/*
** type_code 3 and 4 are for workspaces
*/
if ((@delete_config_val = 0) and ((@type_code = 3) or (@type_code = 4))) 
begin
	/*
	**  Get the id of the workspace
	*/
	select @value = id from sysobjects where name = @str1
	if (@value is NULL)
	begin
		select @thisdbname = db_name()
		/*
		** 18475, "Workspace %1! does not exist in database %2!",
		*/
		raiserror 18475, @str1, @thisdbname
		return (1)
	end

	/*
	** Avoid using the same object for the scan and text workspace.
	*/
	if (@type_code = 3)
		select @type_code1 = 4
	else
		select @type_code1 = 3

	if exists(select 1 from dbcc_config where
		type_code in (3, 4) and 
		type_code != @type_code and
		stringvalue = @str1 and 
		(dbid = 0 or @dbid = 0 or dbid = @dbid))
	begin
		select @wsname = type_name from dbcc_types
		where type_code = @type_code1

		/*
		** The workspace %1! is already configured as %2!. Use 
		** sp_dbcc_configreport to display the current configuration 
		** and sp_dbcc_updateconfig to correct the problem.
		*/
		raiserror 19858, @str1, @wsname
		return (1)
	end

	select @stringvalue = @str1
	select @value = id from sysobjects where name = @stringvalue
end

/*
** type_code 1 is for max worker processes
** type_code 7 is for OAM count threshold
** type_code 8 is for IO error abort
** type_code 9 is for linkage error abort
** type_code 10 is for enable automatic workspace expansion
** type_code 11 is for enable dbcc_counter inserts
** type_code 12 is for enable excluded faults inserts
** Validate @str1 parameter (it is 'delete' if the user wants to delete the
** configuration value.)
*/
if ((@delete_config_val = 0) and @type_code in (1, 7, 8, 9, 10, 11, 12))
begin
	select @ret = 0
	/*
	** @ret will be 1 if @value is valid
	*/
	if (patindex("%[a-z,A-Z]%", @str1) = 0)
	begin
		exec @ret = sp_aux_getsize @str1, @value output
	end
	if ((@ret = 0)
	 OR (@type_code in (10, 11, 12) AND @value not in (0,1)))
	begin
		/*
		** 18476, "'%1!' is not a valid value for '%2!'."
		*/
		raiserror 18476, @str1, @type
       	return (1)
	end
	select @stringvalue = null
end

/*
** Check if worker process count is between 1 and 128.
** If @value is out of range and dbname was provided, we
** can reset it to an appropriate value and, while doing so,
** we can ensure that it does not exceed the number of devices.
** If dbname was not provided and @value is out of range,
** we will exit with an informative error message.
*/
if ((@delete_config_val = 0)  and (@type_code = 1) 
	and ((@value < 1) or (@value > 128)))
begin
        if (@dbname_is_null = 1)
        begin
                /*
                ** 18991, "Illegal value for 'max worker processes'
                ** encountered. Choose a value between 1 and 128 inclusive."
                */
                raiserror 18991
                return (1)
        end
        else if @is_archivedb = 0
        begin
                select @value = count(distinct dev.vdevno)
                from    master..sysdevices dev,
			master..sysusages u
        	where   u.dbid = @dbid
			and u.vdevno = dev.vdevno
			and dev.cntrltype = 0
	end
	else
	begin
		select @scratchdb = convert(sysname, char_value)
		from master.dbo.sysattributes
		where   class=28
			and object_type="D"
			and object=@dbid
			and attribute=0
		if (db_id(@scratchdb) is not null)
		begin
			select @qrystr = 
			'select @value = count(distinct dev.vdevno) ' +
			'from  master..sysdevices dev, '+
			@scratchdb + '.dbo.sysaltusages sa '+
			'where sa.dbid=@dbid and ' +
			'sa.location=5 and ' +
			'dev.status & 4 = 4 and ' +
			'sa.vdevno = dev.vdevno '
			exec (@qrystr)
		end
	end
end

/*
** Get the name and size of private cache used by dbcc
** Verify that the cache exists by validating against 
** master.dbo.sysconfigures config value 19 is for cache
** and make sure it is not an instance only cache.
** If the user is trying to delete the configuration value,
** @str1 will be 'delete' and @str2 will be null.
*/
if ((@type_code = 2) and (@delete_config_val = 0))
begin
	/*
	** config value 19 is for cache
	*/
	if (not exists (select * from master.dbo.sysconfigures where
				config = 19 and name = @str1))
	begin
		/*
		** 18477, "%1! is not a valid cache name"
		*/
		raiserror 18477, @str1
		return (1)
	end

	exec @ret = sp_aux_getsize @str2, @value output
	if (@ret = 0)
	begin
		/*
		** 18478, "The specified size for cache %1! is not valid"
		*/
		raiserror 18478, @str1
		return (1)
	end
	select @stringvalue = @str1
end

/*
** If name of this database is not in the dbcc_config table
** add it here.
*/

begin transaction

/* type_code = database_name */
if not exists (select * from dbcc_config 
		where dbid = @dbid and type_code = 6 
		and stringvalue = @dbname)
begin
	if exists (select * from dbcc_config where dbid = @dbid)
	begin
		declare @old_dbname	varchar(255)
		declare @cmd_name	varchar(40)
		
		rollback transaction

		/*
		** The database might have been dropped or renamed.
		** Ask the user to run sp_dbcc_deletedb <dbid>
		** to get rid of the history and later update the 
		** configuration.
		**
		** 18635, "The database '%1!' with id %2! was configured as 
		** '%3!' in dbccdb database. Please delete the configuration 
		** and history using the command '%4!' and retry the 
		** sp_dbcc_updateconfig command."
		*/
		exec sp_getmessage 18635, @msg output
		select @old_dbname = stringvalue from dbcc_config 
					where dbid = @dbid and type_code = 6
		select @cmd_name = 'sp_dbcc_deletedb  ' + '"' + convert(varchar(10), @dbid) + '"' 
		print @msg, @dbname, @dbid, @old_dbname, @cmd_name
		return (1)
	end

	if ((@@error != 0) or (@@transtate != 0))
	begin
		goto abort_tran
	end

	/*
	** 6 is for database name and 5 is for operation sequence number.
	*/
	insert into dbcc_config values(@dbid, 6, null, @dbname)

	if ((@@error != 0) or (@@transtate != 0))
	begin
		goto abort_tran
	end

	insert into dbcc_config values(@dbid, 5, 0, null)

	if ((@@error != 0) or (@@transtate != 0))
	begin
		goto abort_tran
	end
end

/*
** Update the table with the new configuration parameters or delete 
** the specified configuration parameter for the given database.
*/
if exists (select * from dbcc_config 
		where dbid = @dbid and type_code = @type_code)
begin
	/* If we end up in here, we found the entry in dbcc_config */
	if (@delete_config_val = 0)
	begin
		update dbcc_config 
			set value = @value, stringvalue = @stringvalue
			where dbid = @dbid and type_code = @type_code

		if ((@@error != 0) or (@@transtate != 0))
		begin
			goto abort_tran
		end	
	end
	else
	begin	
		delete from dbcc_config
			where dbid = @dbid and type_code = @type_code

		if ((@@error != 0) or (@@transtate != 0))
		begin
			goto abort_tran
		end
		
	end
end
else
begin
	/*  
	** If we end up in here, the configuration type for the given
	** database does not exist in dbcc_config.
	*/
	if (@delete_config_val = 1)
	begin
                /*
                ** If the user gave us an null @dbname parameter, and we
                ** end up in here, he is trying to delete a default
                ** configuration value; however, there is no
                ** corresponding entry in dbcc_config.
                */
                if (@dbname_is_null = 1)
                begin
                        /* 
			** 19003, "dbcc configuration parameter '%1!' does
			** not have a default value.  The delete request
			** has been ignored."
			*/
                        exec sp_getmessage 19003, @msg output
                        print @msg, @type
                end
                else
                begin
                        /*
                        ** The user wants to delete a configuration value
                        ** for a database that has no entry for that type
                        ** in dbcc_config.
                        */

                        /* 18994, "The dbcc configuration of '%1!' for
                        ** database %2! does not exist.  Your request to
                        ** delete the corresponding configuration value
                        ** has been ignored."
                        */
                        exec sp_getmessage 18994, @msg output
                        print @msg, @type, @dbname
                end

                rollback transaction
                return (1)
        end
	else
	begin
		insert into dbcc_config 
			values (@dbid, @type_code, @value, @stringvalue)

                if ((@@error != 0) or (@@transtate != 0))
                begin
                        goto abort_tran
                end

	end
end

commit transaction

/*
** We've completed the user's request, now we will print an informative
** message to let her know that the mission was accomplished.
*/

if (@dbname_is_null = 1)
begin
	/* 
	** If we end up in here, the user has modified a default
	** configuration value.
	*/
	if (@delete_config_val = 1)
	begin
		/*
		** 19001, "The default value of the dbcc configuration
		** parameter '%1!' has been deleted. 
		*/
		exec sp_getmessage 19001, @msg output
		print @msg, @type
		return (0)
	end
	else
	begin
        	/*
        	**  18992, "The default value of the dbcc configuration
		**  parameter '%1!' has been updated."
        	*/
        	exec sp_getmessage 18992, @msg output
        	print @msg, @type 
        	return (0)
	end
end
else if (@delete_config_val = 1)
begin
	/*
	** 19002, "The dbcc configuration of '%1!' for database %2!
	** has been deleted."
	*/
	exec sp_getmessage 19002, @msg output
	print @msg, @type, @dbname
	return (0)
end
else	
begin 
        /*
        ** 18993, "The value of the dbcc configuration parameter '%1!' for
	** database %2! has been updated." 
        */
        exec sp_getmessage 18993, @msg output
        print @msg, @type, @dbname
        return (0)
end

abort_tran:
	select @error_num = convert(varchar, @@error)
	/* 
	** 19027, 'Aborted %1! operation due to error '%2!'.'
	*/
	raiserror 19027, 'sp_dbcc_updateconfig', @error_num
	rollback transaction
	return (1)

go
grant execute on sp_dbcc_run_updateconfig to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_summaryreport')
begin
	drop procedure sp_dbcc_run_summaryreport
end
go
print "Installing sp_dbcc_run_summaryreport"
go


/*
** Messages for "sp_dbcc_run_summaryreport"    
** 17260, "Can't run %1! from within a transaction."
** 18490, "'%1!' is an invalid DBCC operation. The valid operations are %2!."
** 18491, "No %1! on DBCC operations available."
** 19818, "Local temporary database '%1!' is skipped for this operation. The
**         database is accessible from the owner instance '%2!' only."
*/
create procedure sp_dbcc_run_summaryreport 
( 
		@dbname  varchar(255) = null,
		@date datetime = null,
		@op_name varchar(30) = null,
		@display_recommendations int = null
)
as
declare  @dbid 		smallint 	/* dbid of the database */
declare  @hour 		int
declare	 @min  		int 
declare  @sqlstatus1 	int 
declare  @sqlstatus2 	int 
declare  @uid 		int 
declare  @op_type 	int
declare  @msg 		varchar(255)
declare  @separator 	varchar(80)	/* Section separator line */
declare  @alt_separator varchar(80) 	/* Alternative section separator line 
					** to be used if we have nested sections
					*/
declare  @valid_opnames varchar(255) 
declare  @username 	varchar(255) 
declare  @opid 		int
declare  @seq 		int
declare  @rtrn_status 	int		/* 1 if no report was generated */
declare  @report_name 	varchar(30)	

declare  @dbname_isnull int
declare	 @curdbid	int

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        raiserror 17260, "sp_dbcc_summaryreport"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

if (@op_name is not null)
begin
	/* 
	** Add new checkstorage related commands to this test as they get 
	** supported. 
	*/

	select  @valid_opnames = "'checkstorage' and 'checkverify'"
	if (charindex("'"+@op_name + "'",  @valid_opnames) = 0)
	begin
		/*
		** 18490, "'%1!' is an invalid DBCC operation. The valid operations are %2!."
		*/
		raiserror 18490, @op_name, @valid_opnames
		return (1)
	end
end

select @dbname_isnull = 1

if (@dbname is not null)
begin
    select @dbname_isnull = 0
    exec  @rtrn_status = sp_validatedb  @dbname, @dbid output
    if (@rtrn_status = 1 or @dbid is null)
	return(1)
end

if (@date is null)
begin
	select @date = getdate()
end

/* Initializations */
select @separator = replicate ('=', 80)
select @alt_separator = replicate ('*', 80)
select @report_name = "summary report"


/* Create table #dbcc_db_list with dbname and dbid for all required databases */
select d.dbid, d.name as dbname
  into #dbcc_db_list
  from master..sysdatabases d
 where (d.name = @dbname or @dbname is null)
   and exists ( select * from dbcc_operation_log o
		 where o.dbid = d.dbid and o.finish >= o.start )
 

create table #oplist(optype_id int, opname varchar(30))

insert #oplist(optype_id, opname)  
	values (1, "checkdeviceinfo")

insert #oplist(optype_id, opname)  
	values (2, "checkstorage")

insert #oplist(optype_id, opname)  
	values (3, "checkverify")

insert #oplist(optype_id, opname)  
	values (4, "checkrepair")

insert #oplist(optype_id, opname)  
	values (5, "checkrepair rebuild_OAM")

insert #oplist(optype_id, opname)  
	values (6, "checkrepair rebuild_GAM")

insert #oplist(optype_id, opname)  
	values (7, "load database")

/* Get the list of operation performed */
select distinct o.optype_id, o.opname 
  into #cur_oplist
from #oplist o, dbcc_operation_log l
where o.optype_id = l.optype
  and (@op_name is null or o.opname = @op_name)

declare cursor_oplist cursor  for 
	select optype_id, opname from #cur_oplist 

create table  #dbcc_operations(dbid  smallint, id int null, seq smallint null, 
			       st_time  varchar(20), end_time varchar(10), 
			       opid smallint, username varchar(255), soft int, 
			       hard int, text_cols int null, abort_cnt int null)
create table #tempdbids(dbid int)

/* @rtrn_status will be set to 0 of there are results to report */
select @rtrn_status = 1

open cursor_oplist
fetch cursor_oplist into @op_type, @op_name
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0)
begin
   /*
   ** Handle the results of checkstorage and checkverify operations here.
   ** For other operations add code when they get supported.
   */
   if (@op_type = 2 or @op_type = 3)
   begin
	/*
	** collect the start, finish times, operation id and name of the
	** user who ran dbcc operations into #dbcc_operations table
	** The end_time is in the format hour:minute:second
	*/
	insert #dbcc_operations(dbid, id, seq, st_time, end_time, opid, 
				username, soft, hard, text_cols, abort_cnt)
		select 	ol.dbid, ol.id, ol.seq, 
			convert(varchar(10), ol.start, 101) + " " +
			convert(varchar(10), ol.start, 108), 
			convert(varchar(10), ol.finish, 108), 
			ol.opid, isnull(suser_name(ol.suid), 'suid ' +
                        convert(char(5), ol.suid)), 0, 0, 0, 0
		from 	dbcc_operation_log ol, #dbcc_db_list dl
		where 	ol.optype = @op_type
		and  	ol.dbid = dl.dbid
		and 	ol.finish is not null
		and  	ol.start <= ol.finish 
		and  	ol.finish <= @date


	/*
	** Update the table with values from dbcc_operation_results
	*/
	update #dbcc_operations set hard =  r.intvalue
		from 	#dbcc_operations o, dbcc_operation_results r 
		where 	r.dbid = o.dbid and r.opid = o.opid 
		and 	r.type_code = 1000
		and	r.optype = @op_type
		and	(o.seq is null or r.seq = o.seq)
	update #dbcc_operations set soft =  r.intvalue
		from 	#dbcc_operations o, dbcc_operation_results r 
		where 	r.dbid = o.dbid 
		and 	r.opid = o.opid and r.type_code = 1001
		and	r.optype = @op_type
		and	(o.seq is null or r.seq = o.seq)
	update #dbcc_operations set abort_cnt =  r.intvalue
		from 	#dbcc_operations o, dbcc_operation_results r 
		where 	r.dbid = o.dbid 
		and 	r.opid = o.opid and r.type_code = 1002
		and	r.optype = @op_type
		and	(o.seq is null or r.seq = o.seq)
	update #dbcc_operations set text_cols =  r.intvalue
		from 	#dbcc_operations o, dbcc_operation_results r 
		where 	r.dbid = o.dbid 
		and 	r.opid = o.opid and r.type_code = 1007
		and	r.optype = @op_type
		and	(o.seq is null or r.seq = o.seq)
	
	if exists (select * from #dbcc_operations)
	begin
		/* set @rtrn_status to 0 since we do have something to report */
		select @rtrn_status = 0

		print "DBCC Operation : %1!", @op_name
		print @separator
		if (@op_type = 2) 
		begin
			/* report on checkstorage operation */
			select  "Database Name" = db_name(dbid), 
				"Start time" = st_time,
				"End Time" = end_time, 
				"Operation ID" = opid, 
				"Hard Faults" = hard,
				"Soft Faults" = soft,
				"Text Columns" = text_cols,
				"Abort Count" = abort_cnt,
				"User Name" = username 
				from #dbcc_operations
				order by  dbid, st_time
		end
		else if (@op_type = 3)
		begin
			/* report on checkverify operation */
			select  "Database Name" = db_name(dbid), 
				"Start time" = st_time,
				"End Time" = end_time, 
				"Operation ID" = opid, 
				"Run Srl" = seq, 
				"Table Name" = object_name(id, dbid), 
				"Table Id" = id, 
				"Hard Faults" = hard,
				"Soft Faults" = soft,
				"User Name" = username 
				from #dbcc_operations
				order by  dbid, st_time, opid, seq
		end

		/*
		** Did the user request fix recommendations?  If so,
		** call sp_dbcc_run_recommendations; however, do so only
		** if we are reporting on a checkstorage run. 
		*/
		if ((@display_recommendations is not null) and
		    (@display_recommendations = 1) and 
		    (@op_type = 2))
		begin
			declare cursor_dbcc_operations cursor for
				select db_name(dbid), max(opid)
					from #dbcc_operations
					group by dbid
			open cursor_dbcc_operations

			print @alt_separator
			/* Initial Fetch */
			fetch cursor_dbcc_operations into @dbname, @opid

			select @sqlstatus2 = @@sqlstatus
			while (@sqlstatus2 = 0)
			/*
			**  To give the user a list of helpful corrective
			**  recommendations based on the faults reported
			**  by checkstorage, call sp_dbcc_run_recommendations.
			*/	
			begin
				print ''
				/*
				** 19037, 'Recommended corrective action
				** for faults reported by the checkstorage
				** run corresponding to database %1!, opid
				** %2!:'
				*/
				exec sp_getmessage 19037, @msg output
				print @msg, @dbname, @opid
				print ''
				exec sp_dbcc_run_recommendations 
							@dbname, null, @opid
				print ''
				print @alt_separator
				print ''
				fetch cursor_dbcc_operations into @dbname, 
								  @opid
				select @sqlstatus2 = @@sqlstatus
			end
			
			close cursor_dbcc_operations
			deallocate cursor cursor_dbcc_operations
		end
				
	end
	print  ""

	/* Print msg for all the local tempdbs skipped earlier from report */
	if exists (select * from #tempdbids)
        begin
		declare dbidcursor cursor for select dbid from #tempdbids
		open dbidcursor

		/* Initial Fetch */
		fetch dbidcursor into @curdbid

		select @sqlstatus2 = @@sqlstatus
		while (@sqlstatus2 = 0)
		begin
			exec sp_check_remote_tempdb @curdbid, 'skip'
			print ""

			fetch dbidcursor into @curdbid 
			select @sqlstatus2 = @@sqlstatus
		end
			
		close dbidcursor
		deallocate cursor dbidcursor
	end

	print @separator
	truncate table #dbcc_operations
	truncate table #tempdbids
   end  /*  (@op_type = 2 or 3)  = checkstorage or checkverify operations */

   fetch cursor_oplist into @op_type, @op_name
   select @sqlstatus1 = @@sqlstatus
end
close cursor_oplist

deallocate cursor cursor_oplist
drop table #oplist
drop table #cur_oplist
drop table #dbcc_db_list
drop table #dbcc_operations
drop table #tempdbids

if (@rtrn_status != 0)
begin
	/*
	** 18491, "No %1! on DBCC operations available"
	*/
        raiserror 18491, @report_name
end

return (@rtrn_status)
go
grant execute on sp_dbcc_run_summaryreport to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_runcheck')
begin
	drop procedure sp_dbcc_run_runcheck
end
go
print "Installing sp_dbcc_run_runcheck"
go

/*
** This procedure runs "dbcc checkstorage" on the specified database
** and later runs the procedure passed by the user. If @user_proc is
** null, it will run sp_dbcc_summaryreport
*/

/*
** Messages for "sp_dbcc_run_runcheck"    
** 17421, "No such database -- run sp_helpdb to list databases."
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
*/
create procedure sp_dbcc_run_runcheck 
( 
		@dbname  varchar(255), 
		@user_proc  varchar(255) = null
)
as
declare @dbid int 			/* dbid of the database */
declare @dbo int                      	/* is the owner of the database ? */
declare @retval int                     /* id of the owner of the database */
declare @cur_date  datetime		/* current time */
declare @dummy int

if @@trancount = 0
begin
        set chained off
end
 
set transaction isolation level 1

set nocount on

/*
**  Verify the database name and get the @dbid
*/
select @dbid = dbid
        from master.dbo.sysdatabases
                where name = @dbname
 
/*
**  If @dbname not found, say so and return
*/
if @dbid is NULL
begin
	/*
	** 17421, "No such database -- run sp_helpdb to list databases."
	*/
	raiserror 17421
	return (1)
end

exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname

/*
**  Only the Database Owner (DBO) or
**  Accounts with SA role can execute it.
*/
if ((@dbo != 1) and (charindex("sa_role", show_role()) < 1))
begin
	/*
	** Call proc_role() to generate the appropriate
	** audit record.
	*/
	select @dummy = proc_role("sa_role")
	/*
	** 17230, "You must be the System Administrator (SA) or
	** the Database Owner (dbo) to execute this procedure."
	*/
	raiserror 17230
	return (1)
end
/*
** run dbcc checkstorage operation
*/

dbcc checkstorage(@dbname)

/*
** checkstorage returned with some error. dbcc has printed
** the error message; so return
*/
if (@@error != 0)
	return(1)
/*
** Check if checkstorage operation reported soft errors
** If so, run dbcc checkverify. This is not valid now because
** dbcc checkverify is not ready yet.
*/

dbcc checkverify(@dbname)

/*
** Generate a report if user_proc is null. Otherwise
** execute user_proc
*/

if (@user_proc is null)
begin
	select @cur_date = getdate()
	select @user_proc = "sp_dbcc_run_summaryreport"
	exec @retval = @user_proc @dbname
end
else
begin
	exec @retval = @user_proc
end

if (@retval != 0)
	return(1)
else
	return(0)
go
grant execute on sp_dbcc_run_runcheck to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_statisticsreport')
begin
	drop procedure sp_dbcc_run_statisticsreport
end
go
print "Installing sp_dbcc_run_statisticsreport"
go


/*
** Messages for "sp_dbcc_run_statisticsreport"    
** 17260, "Can't run %1! from within a transaction."
** 18488, "Object name must be accompanied by database name."
** 18491, "No %1! on DBCC operations available."
** 18489, "Generating 'Statistics Report' for object %1! in database %2!."
*/
create procedure sp_dbcc_run_statisticsreport 
( 
		@dbname  varchar(255) = null,
		@objectname  varchar(255) = null,
		@date  datetime = null
)
as
declare  @dbid smallint 		/* dbid of the database */
declare  @sqlstatus1 int, @sqlstatus2 int
declare  @objid int 
declare  @indid smallint 
declare  @partitionid int 
declare  @objname varchar(255) 
declare	 @optype_for_checkstorage  int
declare	 @opid	int
declare	 @ret	int
declare  @rtrn_status int	/* 1 if no report was generated */
declare  @msg varchar(1024) 
declare  @report_name varchar(30)  
declare  @dbname_isnull int
declare	 @is_remote_tdb int

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        exec sp_getmessage 17260, @msg output
        raiserror 17260 @msg, "sp_dbcc_statisticsreport"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

create table  #dbcc_db_list(dbid  smallint, dbname  varchar(255))
declare cursor_dbcc_db_list cursor  
	for select  dbid, dbname from #dbcc_db_list 

select @report_name = "statistics report"

/*
**  insert dbname and dbid for all valid databases in #dbcc_db_list
*/
insert #dbcc_db_list(dbid, dbname) 
	select d.dbid, d.name 
	from master..sysdatabases d
	where exists ( select * from dbcc_counters c 
				where c.dbid = d.dbid ) 

select @dbid = null

/*
** If the dbname was specified by the user, validate it 
*/
if (@dbname is not null)
begin
	select @dbname_isnull = 0

	/*
	** If the dbname is valid, delete other databases from
	** #dbcc_db_list. Otherwise return error
	** because we are interested in only one database
	*/
    	exec  @ret = sp_validatedb  @dbname, @dbid output
    	if (@dbid is not null)
		delete from #dbcc_db_list where dbid != @dbid
	else
		return(1)
end
else
begin
	select @dbname_isnull = 1

	if (@objectname is not null)
	begin
		/*
		** 18488, "Object name must be accompanied by database name."
		*/
		raiserror 18488
		return (1)
	end
end
 
create table #dbcc_obj_list(objid int, objname varchar(255) null)
declare cursor_obj_list cursor for 
	select objid, objname from #dbcc_obj_list 
	 order by objname, objid

create table  #stat_info1(objid  int, indid smallint, partitionid int, 
			type_code  int, value real null)
create table  #stat_info2(objid  int, indid smallint, partitionid int,
			type_code  int, devid int, value real null)

/*
** for these type_codes in #counter_sum_types, calculate the sum of
** value for different partitionid, devid
*/
create table  #counter_sum_types(type int) 
declare cursor_counter_sum_types cursor for 
	select type from #counter_sum_types
insert #counter_sum_types(type) values(5000)
insert #counter_sum_types(type) values(5001)
insert #counter_sum_types(type) values(5006)
insert #counter_sum_types(type) values(5020)
insert #counter_sum_types(type) values(5021)
insert #counter_sum_types(type) values(5022)
insert #counter_sum_types(type) values(5023)
insert #counter_sum_types(type) values(5024)

/*
** for these type_codes in #counter_max_types, calculate the max of
** value for different partitionid, devid
*/
create table  #counter_max_types(type int) 
declare cursor_counter_max_types cursor for 
	select type from #counter_max_types
insert #counter_max_types(type) values(5007)
insert #counter_max_types(type) values(5008)
insert #counter_max_types(type) values(5009)

/*
** for these type_codes in #counter_reg_types, the value is stored for
** each partitionid, devid. 
*/
create table  #counter_reg_types(type int) 
declare cursor_counter_reg_types cursor for 
	select type from #counter_reg_types
insert #counter_reg_types(type) values(5002)
insert #counter_reg_types(type) values(5003)
insert #counter_reg_types(type) values(5004)
insert #counter_reg_types(type) values(5005)
insert #counter_reg_types(type) values(5010)
insert #counter_reg_types(type) values(5011)
insert #counter_reg_types(type) values(5012)
insert #counter_reg_types(type) values(5013)
insert #counter_reg_types(type) values(5014)
insert #counter_reg_types(type) values(5016)
insert #counter_reg_types(type) values(5015)
insert #counter_reg_types(type) values(5017)
insert #counter_reg_types(type) values(5018)
insert #counter_reg_types(type) values(5019)

select @optype_for_checkstorage = 2

select @rtrn_status = 1
/*
** for each database in #dbcc_db_list, generate the report
*/
open cursor_dbcc_db_list
fetch cursor_dbcc_db_list into @dbid, @dbname
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0) 
begin
	/*
	** Get the opid for this database corresponding to the date 
	** specified. If none, skip this database
	*/
	exec sp_getopid @dbname, @date, @optype_for_checkstorage, 
					@opid output  
	if (@opid is null)
	begin
		fetch cursor_dbcc_db_list into @dbid, @dbname
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	/*
	** In SDC, if dbname is not specified, skip local tempdbs that
	** are only accessible from remote instances.
	*/
	if (@@clustermode = "shared disk cluster" and @dbname_isnull = 1)
	begin
		exec @is_remote_tdb = sp_check_remote_tempdb @dbid, 'skip'
		if (@is_remote_tdb = 1)
		begin
			print ""
			fetch cursor_dbcc_db_list into @dbid, @dbname
			select @sqlstatus1 = @@sqlstatus
			continue
		end
	end

	/*
	** Prepare a list of objects on which statistics information
	** to be reported. If the list is empty skip this database.
	** If object_name() is null, it indicates that the object was
	** dropped after the checkstorage operation. Use the object id
	** in the reports.
	*/
	if (@objectname is not null)
	begin
		insert #dbcc_obj_list(objid, objname)
			select distinct id, 
				isnull(object_name(id, @dbid), 
					convert(varchar,id))
			from dbcc_counters
			where dbid = @dbid 
			and object_name(id, @dbid) = @objectname
			and opid = @opid
	end
	else
	begin
		insert #dbcc_obj_list(objid, objname)
			select distinct id, 
				isnull(object_name(id, @dbid), 
					convert(varchar,id))
			from dbcc_counters 
			where dbid = @dbid and opid = @opid
	end

	if exists (select * from #dbcc_obj_list) 
	begin

	    /*
	    ** Calculate the sum of values for all devices
	    ** for this (objid,indid,partitionid) 
	    */
	    insert #stat_info1(objid, indid, partitionid, type_code, value)
		select c1.id, c1.indid, c1.partitionid, c1.type_code, 
			sum(c1.value)
		from dbcc_counters c1, #counter_sum_types st, 
				#dbcc_obj_list ol
		where c1.opid = @opid and c1.type_code = st.type 
			and c1.dbid = @dbid 
			and c1.id = ol.objid 
		group by c1.id, c1.indid, c1.partitionid, c1.type_code

	    /*
	    ** Calculate the max of values for all devices
	    ** for this (objid,indid,partitionid) 
	    */
	    insert #stat_info1(objid, indid, partitionid, type_code, value)
		select c1.id, c1.indid, c1.partitionid, c1.type_code, 
			max(c1.value)
		from dbcc_counters c1, #counter_max_types mt, 
				#dbcc_obj_list ol
		where c1.opid = @opid 
			and c1.type_code = mt.type and c1.dbid = @dbid 
			and c1.id = ol.objid 
		group by c1.id, c1.indid, c1.partitionid, c1.type_code

	    /*
	    ** Get the values for all devices
	    ** for this (objid,indid,partitionid) 
	    */
	    insert #stat_info2(objid, indid, partitionid, type_code, devid, 
					value)
		select c1.id, c1.indid, c1.partitionid, c1.type_code, c1.devid,
				c1.value
		from dbcc_counters c1, #dbcc_obj_list ol,
				 #counter_reg_types rt
		where c1.opid = @opid 
			and c1.type_code = rt.type and c1.dbid = @dbid 
			and c1.id = ol.objid 

	    open cursor_obj_list
	    fetch cursor_obj_list into @objid, @objname
	    select @sqlstatus2 = @@sqlstatus
	    while (@sqlstatus2 = 0) 
	    begin
		/*
		** 18489, "Generating 'Statistics Report' for object %1! in database %2!."
		exec sp_getmessage 18489, @msg output
		*/
        	print "Statistics Report on object %1! in database %2!", 
				@objname, @dbname
		print ""

		select distinct "Parameter Name" = convert(varchar(50), t.type_name), 
			"Index Id" = convert(varchar(3), st1.indid),
			"Partition Id" = convert(varchar(10), st1.partitionid),
			"Value" = convert(varchar(20), value)
			from #stat_info1 st1, dbcc_types t
			where st1.objid = @objid 
			and st1.type_code = t.type_code
			order by st1.indid, st1.partitionid
		print ""
		select 	distinct "Parameter Name" = convert(varchar(50), t.type_name),
			"Index Id" = convert(varchar(3), st2.indid),
			"Partition Id" = convert(varchar(10), st2.partitionid),
			"Value" = convert(varchar(20), value), 
			"Dev_name" = d.name
			from #stat_info2 st2, dbcc_types t,
				 master..sysdevices d
			where st2.objid = @objid 
			  and ((d.status & 6) != 0) 
			  and d.vdevno = st2.devid
			  and st2.type_code = t.type_code
			order by st2.indid, st2.partitionid
		print ""

	        fetch cursor_obj_list into @objid, @objname
		select @sqlstatus2 = @@sqlstatus
	    end   /* while (@sqlstatus2 = 0 ) */
	    select @rtrn_status = 0
	    close cursor_obj_list
	end   /* if exists (select * from #dbcc_obj_list) */

	truncate table #stat_info1
	truncate table #stat_info2
	truncate table #dbcc_obj_list
	fetch cursor_dbcc_db_list into @dbid, @dbname
	select @sqlstatus1 = @@sqlstatus
end

close cursor_dbcc_db_list
deallocate cursor cursor_obj_list
deallocate cursor cursor_dbcc_db_list
drop table #stat_info1
drop table #stat_info2
drop table #dbcc_obj_list
drop table #dbcc_db_list

if (@rtrn_status != 0)
begin
	/*
	** 18491, "No %1! on DBCC operations available"
	*/
	raiserror 18491, @report_name
	return (1)
end

return (0)
go
grant execute on sp_dbcc_run_statisticsreport to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_faultreport')
begin
	drop procedure sp_dbcc_run_faultreport
end
go
print "Installing sp_dbcc_run_faultreport"
go

/*
** Generate a report on faults reported on the specified
** objects. If objectname is null, report faults on all objects
** If datetime is null, report faults from the last completed checkstorage
** operation.
** If dbcc exclusions are recorded in dbccdb or specified using the 
** exclusion_faults, exclusion_tables or exclusion_combo parameters
** those faults are not considered for reporting. The exclusion_mode
** parameter specifies if the persistent exclusions recorded in dbcc db
** are to be ignored or extended with the exclusions specified here.
*/

/*
** Messages for "sp_dbcc_run_faultreport"    
** 17260, "Can't run %1! from within a transaction."
** 18476, "%1! is not a valid value for '%2!'."
** 18488, "Object name must be accompanied by database name."
** 18487, "Generating 'Fault Report' for object %1!, indid %2!, partition id %3! in database %4!."
** 18491, "No %1! on DBCC operations available."
** 17461, "Object does not exist in this database."
** 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."
** 19011, "Invalid fault type '%1!', ignoring token '%2!'."
** 19016, "Ignoring the exclusion lists since the exclusion mode is not specified."
** 19017, "You must specify at least one of the exclusion lists to run this report with the 'extend' exclusions mode." 
** 19018, "Invalid exclusion mode '%1!' specified. Valid modes are 'ignore', 'extend', and null."
** 19019, "Error '%1!' while inserting the temporary '%2!' exclusion '%3!'. Aborting fault report. " 
** 18302, "User '%1!' is not a valid user in the '%2!' database."
*/
create procedure sp_dbcc_run_faultreport 
( 
		@report_type  varchar(8) = 'short',
		@dbname  varchar(255) = null,
		@objectname  varchar(255) = null,
		@date  datetime = null,
		@hard_only tinyint = 0,
		@exclusion_mode varchar(8) = null, 
		/* ignore | extend */
		@exclusion_faults  varchar(500) = null,
		@exclusion_tables  varchar(500) = null,
		@exclusion_combo   varchar(500) = null,
		@display_recommendations int = null,
		@opid int = null,
		@fault_type_in int = null

)
as
declare  @dbid 	smallint 		/* dbid of the database */
declare  @objid int 			/* objid in the fault row */
declare  @indid smallint 		/* indid in the fault row */
declare  @partitionid int 		/* partitionid in the fault row */
declare  @objname varchar(767) 
declare  @opid_orig int			/* The opid specified as input */
declare	 @optype_for_checkstorage  int
declare	 @optype_for_checkverify  int
declare	 @optype_for_checkrepair  int
declare	 @ret	int
declare	 @error	int
declare  @msg varchar(1024) 
declare  @rtrn_status int	/* 1 if no report was generated */
declare  @report_name varchar(30)  
declare	 @sqlstatus1  int, @sqlstatus2 int
declare	 @sqlstatus3 int, @sqlstatus4  int
declare	 @type_code1	int, @type_code2 int
declare	 @intval  int
declare	 @faultid  int
declare	 @binval  varbinary(44) 
declare	 @realval real 
declare	 @strval varchar(255) 
declare	 @desc1 varchar(255), @desc2  varchar(255), @faultname varchar(255) 
declare	 @dateval datetime

declare @ts_str         varchar(14)
declare @stat           int
declare @stat2          int
declare @priv_stat      int
declare @stat_str       varchar(6)
declare @stat2_str      varchar(6)
declare @priv_stat_str  varchar(6)
declare @psz 		smallint
declare @pvers 		smallint
declare @numrid 	smallint
declare @ncfs 		smallint
declare @ndeleted 	smallint
declare @insfs 		int
declare @pno            int
declare @pgleft         int
declare @next_pno       int
declare @prev_pno       int
declare @pindid 	smallint 	/* indid in the page header */
declare @ppartitionid   int		/* partitionid on the page header */
declare @ts_lo          int
declare @ts_hi          int
declare @nextrno        int
declare @level          int
declare @freeoff        int
declare @minlen         int
declare @reserv         int
declare @valid_objid	int
declare @valid_user	int
declare @username       varchar(32)
declare @maxlen		int
declare @rno		int
declare @col		int
declare @fobjid 	int 		/* objid in the fault param */
declare @findid 	smallint 	/* indid in the fault param */
declare @fpartitionid 	int 		/* partitionid in the fault param */

declare	@comma  	char(1), 
	@colon  	char(1),
	@clist 		varchar(2000),
	@i		smallint,
	@i2		tinyint,
	@token 		varchar(300),
	@fault_type 	int,
	@table_name 	varchar(255),
	@exclusion_type tinyint,
	@emode 		int  /* this can be 0, 1, 2, or 3. This is a bitmap 
			     ** where the bits are:
			     ** 1 -> use persistent exclusion list
			     ** 2 -> use temporary exclusion list.
			     ** Hence for example a value of 3 would indicate
			     ** use both the persistent and temporary exclusion
			     ** lists. Also 0 would indicate ignore all
			     ** exclusion lists and report all faults.
			     */

declare  @is_remote_tdb int,
	 @dbname_isnull int

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        raiserror 17260, "sp_dbcc_faultreport"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

create table  #dbcc_db_list(dbid  smallint, dbname  varchar(30))
declare cursor_dbcc_db_list cursor  
	for select  dbid, dbname from #dbcc_db_list 

select @report_name = "fault report"

/*
** The default report format is 'short'. If user specifies anything
** other than 'long', set @report_type to 'short'
*/
if (@report_type is not null)
begin
	if ((@report_type != 'long') and (@report_type != 'short'))
	begin
		/*
		** 18476, "%1! is not a valid value for '%2!'."
		*/
        	raiserror 18476, @report_type, 'report type'
		return(1)
	end
end
else
	select @report_type = 'short'


/*
** Exclusion mode when specified can be one of the following:
** 	'ignore'   
**		 indicates that the fault report should ignore exclusions, 
**		 if any, recorded in dbccdb. 
**		 If any of exclusion_faults, exclusion_tables, exclusion_comb 
** 		 are not null these are applied.
**	'extend'	
**		 requires that at least one of exclusion_faults, exclusion_tables
** 		 and exclusion_combo is not null. 
**		 The specified exclusions are applied along with the exclusions
** 		 recorded in dbccdb.
**	null	
**		 this requires that exclusion_faults, exclusion_tables and 
**		 exclusion_combo are all null.
*/

if (@exclusion_mode is null)
begin
	if ((@exclusion_faults is not null) or (@exclusion_tables is not null)
	or  (@exclusion_combo is not null))
		/*
		** 19016, "Ignoring the exclusion lists since the exclusion mode is not specified."
		*/
		raiserror 19016
	select @emode = 1
end
else if (@exclusion_mode = 'ignore')
begin
	if ((@exclusion_faults is not null) or (@exclusion_tables is not null)
	or  (@exclusion_combo is not null))
		select @emode = 2
	else 
		select @emode = 0
end
else if (@exclusion_mode = 'extend')
begin
	if ((@exclusion_faults is null) and (@exclusion_tables is null)
	and  (@exclusion_combo is null))
		/*
		** 19017, "You must specify at least one of the exclusion lists to run this report with the 'extend' exclusions mode."
		*/
		raiserror 19017
	select @emode = 3
end
else
	/*
	** 19018, "Invalid exclusion mode '%1!' specified. Valid modes are 'ignore', 'extend', and null."
	*/
	raiserror 19018, @exclusion_mode


/*
**  insert dbname and dbid for all valid databases in #dbcc_db_list
*/
insert #dbcc_db_list(dbid, dbname) 
	select d.dbid, d.name 
	from master..sysdatabases d
	where exists ( select 1 from dbcc_faults f 
				where f.dbid = d.dbid ) 

select @dbid = null, @valid_user = 0, @opid_orig = @opid

/*
** If the dbname was specified by the user, validate it 
*/
if (@dbname is not null)
begin
	select @dbname_isnull = 0

	/*
	** If the dbname is valid, delete other databases from
	** #dbcc_db_list. Otherwise return error
	** because we are interested in only one database
	*/
    	exec  @ret = sp_validatedb  @dbname, @dbid output
    	if (@dbid is not null)
	begin
		/*
		** Make sure the user is a 'valid user' of the
		** target database.
		*/
		exec @ret = sybsystemprocs.dbo.sp_is_valid_user 
				@valid_user output, @dbname

		if (@ret != 0 or @valid_user = 0)
		begin
			deallocate cursor cursor_dbcc_db_list
			drop table #dbcc_db_list

			if (@ret = 0)
			begin
				select @username = suser_name()
				/*
				** 18302, "User '%1!' is not a valid user
				** in the '%2!' database."
				*/
				raiserror 18302, @username, @dbname
			end

			return (1)
		end

		/*
		** When the object name parameter is specified,
		** check that the object exists.
		*/
		if (@objectname is not null)
		begin
			select @objname = @dbname + ".." + @objectname
			select @valid_objid = object_id(@objname)
			if (@valid_objid is null)
			begin
				/*
				** 17461, "Object does not exist in database."
				*/
				raiserror 17461
				return (1)
			end
		end
		delete from #dbcc_db_list where dbid != @dbid
	end
	else
		return (1)
end
else
begin
	select @dbname_isnull = 1

	if (@objectname is not null)
	begin
		/*
		** 18488, "Object name must be accompanied by database name"
		*/
		raiserror 18488
		return (1)
	end
end
 
/*
** If there are no qualified databases, return
*/
if ((select count(*) from #dbcc_db_list) < 1)
begin
	/*
	** 18491, "No %1! on dbcc operations available"
	*/
        raiserror 18491, @report_name
	return(1)
end

/* 
** temporary table for temp exclusions, we create this unconditionally
** so that the exclusion query does not give any normalization errors 
*/
create table #dbcc_exclusions (
	fault_type	int null,
	table_name	varchar(255) null 
)

/* 
** If we have a temporary exclusion list emode = 2 or 3, 
** we need to process that 
*/
if (@emode & 2 != 0)
begin
	/*
	**  Get the max length for table names.
	*/
	select @maxlen = length from syscolumns
	 where id = object_id("sysobjects")
	   and name = "name"
		
	create unique clustered index cidx on 
		#dbcc_exclusions(fault_type, table_name) 
	  with ignore_dup_key 
	
	/* initialize some variables */
	select @comma = ",", @colon = ":"

	/* parse faults list */
	if ( @exclusion_faults is not null )
	begin
		select @clist = @exclusion_faults 
		while(1 = 1)
		begin
			select @i = charindex(@comma, @clist)
			if (@i = 0) 
				select @token = @clist 
			else
				select @token = substring(@clist, 1, @i - 1)
			select @token = ltrim(rtrim(@token))
		
			select @fault_type = convert(int, @token) 
		
			/* check for error */
			if (@@error != 0) 
			begin
				/*
				** 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."
				*/
				exec sp_getmessage 19010, @msg output
				print @msg, @token
				goto next_faults_token
			end
		
			/* 
			** Validate fault_type, fault_types are stored in 
			** dbcc_types  with type_code starting from 100000.
			** Ignore invalid faults. 
			*/
			if not exists (select 1 from dbcc_types 
					where type_code = @fault_type
					  and type_code >= 100000)
			begin
				/*
				** 19011, "Invalid fault type '%1!', ignoring token '%2!'."
				*/
				exec sp_getmessage 19011, @msg output
				print @msg, @fault_type, @token
				goto next_faults_token
			end
		
			insert into #dbcc_exclusions (table_name, fault_type) 
				values (null, @fault_type)
			/* Error processing */
			select @error = @@error
			if (@error != 0)
			begin
				/* 
				** 19019, "Error '%1!' while inserting the temporary '%2!' exclusion '%3!'. Aborting fault report. " 
				*/
				exec sp_getmessage 19019, @msg output
				print @msg, @error, 'faults' , @fault_type
				return (1)
			end
		
		next_faults_token:

			/* check if we are done */
			if (@i = 0) 
				break
		
			select @clist = substring(@clist, @i + 1, char_length(@clist) - @i)
		end
	end


	/* parse tables list */
	if ( @exclusion_tables is not null )
	begin
		select @clist = @exclusion_tables
		while(1 = 1)
		begin
			select @i = charindex(@comma, @clist)
			if (@i = 0) 
				select @token = @clist 
			else
				select @token = substring(@clist, 1, @i - 1)
			select @token = ltrim(rtrim(@token))
		
			select @table_name = @token
			
			/* check for invalid table name */
			if (valid_name(@table_name, @maxlen) = 0)
			begin
				/*
				** 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."
				*/
				exec sp_getmessage 19010, @msg output
				print @msg, @token
				goto next_table_token
			end

			/* 
			** If user has specified an object name for fault 
			** reporting, then we will be reporting only for
			** the specified object, hence exclusions for other
			** objects are meaningless. 
			*/	
			if ((@objectname is not null) and  
			    (@table_name != @objectname))
			begin
				goto next_table_token
			end

			insert into #dbcc_exclusions (table_name, fault_type) 
				values (@table_name, null)
			/* Error processing */
			select @error = @@error
			if (@error != 0)
			begin
				/* 
				** 19019, "Error '%1!' while inserting the temporary '%2!' exclusion '%3!'. Aborting fault report. " 
				*/
				exec sp_getmessage 19019, @msg output
				print @msg, @error, 'tables', @table_name
				return (1)
			end

			/* If user has specified an object name and we see the 
			** same object in exclusion list then nothing will 
			** be reported. Thus there is no point processing the
			** exclusion list any further.
			*/
			if ((@objectname is not null) and 
			    (@objectname = @table_name))
				break

		next_table_token:

			/* check if we are done */
			if (@i = 0) 
				break
		
			select @clist = substring(@clist, @i + 1, char_length(@clist) - @i)
		end
	end
	
	/* parse combo list */
	if ( @exclusion_combo is not null )
	begin
		select @clist = @exclusion_combo
		while(1 = 1)
		begin
			select @i = charindex(@comma, @clist)
			if (@i = 0) 
				select @token = @clist 
			else
				select @token = substring(@clist, 1, @i - 1)

			select @token = ltrim(rtrim(@token))
		
			/* combo case break into table_name and fault_type */
			select @i2 = charindex(@colon, @token)
			if (@i2 = 0)
			begin
				/*
				** 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."
				*/
				exec sp_getmessage 19010, @msg output
				print @msg, @token
				goto next_combo_token
			end
	
			select @table_name = rtrim(substring(@token, 1, @i2 - 1)),
			       @fault_type = convert(int, 
						substring(@token, @i2 + 1, 
							  char_length(@token) -
									@i2))
		
			/* check for error or invalid table name */
			if ((@@error != 0)  
			 or (valid_name(@table_name, @maxlen) = 0))
			begin
				/*
				** 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."
				*/
				exec sp_getmessage 19010, @msg output
				print @msg, @token
				goto next_combo_token
			end
	
			/* 
			** If user has specified an objectname for fault 
			** reporting, temporary exclusions on other objects
			** are meaningless so skip them.
			*/
			if ((@objectname is not null) and 
			    (@table_name != @objectname))
			begin
				goto next_combo_token
			end

			/* 
			** Validate fault_type, fault_types are stored in 
			** dbcc_types with type_code starting from 100000.
			** Ignore invalid faults. 
			*/
			if not exists (select 1 from dbcc_types 
					where type_code = @fault_type
					  and type_code >= 100000)
			begin
				/*
				** 19011, "Invalid fault type '%1!', ignoring token '%2!'."
				*/
				exec sp_getmessage 19011, @msg output
				print @msg, @fault_type, @token
				goto next_combo_token
			end
		
			insert into #dbcc_exclusions (table_name, fault_type) 
				values (@table_name, @fault_type)
			/* Error processing */
			select @error = @@error
			if (@error != 0)
			begin
				/* 
				** 19019, "Error '%1!' while inserting the temporary '%2!' exclusion '%3!'. Aborting fault report. " 
				*/
				exec sp_getmessage 19019, @msg output
				print @msg, @error, 'combo' , @token
				return (1)
			end
		
		next_combo_token:

			/* check if we are done */
			if (@i = 0) 
				break
		
			select @clist = substring(@clist, @i + 1, 
						  char_length(@clist) - @i)
		end
	end
	
end /* End of processing temporary exclusion list */ 

select @optype_for_checkstorage = 2
select @optype_for_checkverify = 3
select @optype_for_checkrepair = 4

select @rtrn_status = 1
open cursor_dbcc_db_list
fetch cursor_dbcc_db_list into @dbid, @dbname
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0) 
begin 	/* { */
	/*
	** In SDC, if dbname is not specified, skip local tempdbs that
	** are only accessible from remote instances.
	*/
	if (@@clustermode = "shared disk cluster" and @dbname_isnull = 1)
	begin
		exec @is_remote_tdb = sp_check_remote_tempdb @dbid, 'skip'
		if (@is_remote_tdb = 1)
		begin
			fetch cursor_dbcc_db_list into @dbid, @dbname
			select @sqlstatus1 = @@sqlstatus
			continue
		end
	end

	/* 
	** If the user is not a 'valid user' of the target 
	** database, skip the database.
	*/
	exec @ret = sybsystemprocs.dbo.sp_is_valid_user 
			@valid_user output, @dbname

	if (@ret != 0 or @valid_user = 0) 
	begin
		fetch cursor_dbcc_db_list into @dbid, @dbname
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	/*
	** If opid is not specified get the opid for this database corresponding 
	** to the date specified. 
	** If neither an opid is specified nor we get one from above, skip this 
	** database
	*/
	if (@opid_orig is null)
		exec sp_getopid @dbname, @date, @optype_for_checkstorage, 
					@opid output  
	else
		select @opid = @opid_orig

	if (@opid is null)
	begin
		fetch cursor_dbcc_db_list into @dbid, @dbname
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	/* 
	** Create the #fault_info1 table with all fault info 
	** status is never -3, the nullif is used such that the
	** intval column in #fault_info1 is created as a nullable column. 
	*/
	select   objid = id, indid, partitionid, faultid, f.type_code, 
		 intval = nullif(status, -3), faultname = t.type_name
	    into #fault_info1
	    from dbcc_faults f, dbcc_types t
	   where opid = @opid and dbid = @dbid 
	     and f.type_code = t.type_code
	    -- if we only want hard faults, status & 1 != 0 for hard faults
	     and (@hard_only = 0 or status & 1 != 0 )
	    -- if objectname is specified select faults only for that 
	     and ( (@objectname is null) 
		or (object_name(f.id, @dbid) = @objectname))
	    -- if @fault_type_in is specified report only for that fault.
	     and ( (@fault_type_in is null)
		or (f.type_code = @fault_type_in))
	    -- add the persistent exclusion condition if emode is 1 or 3
	     and ((@emode & 1 = 0) 
	       or (
	    		--faults exclusions
			f.type_code not in (select e.fault_type 
					      from dbcc_exclusions e
					     where e.table_name is null
					       and e.dbid in (0, @dbid))
			--tables exclusions
	            and object_name(f.id, @dbid) not in 
					(select e.table_name
				      	   from dbcc_exclusions e
				          where e.fault_type is null 
					    and e.dbid in (0, @dbid))
			--combo exclusions
		    and not exists (select 1 
				      from dbcc_exclusions e
				     where e.dbid in (0, @dbid)
				       and e.table_name = 
					    object_name(f.id, @dbid)
				       and e.fault_type = f.type_code
				       and e.fault_type is not null
				       and e.table_name is not null) ) )
	    -- add the temporary exclusion list if emode is 2 or 3
	    and ((@emode & 2 = 0) 
	      or (
			-- temporary faults exclusions
			f.type_code not in (select e.fault_type 
					      from #dbcc_exclusions e
				             where e.table_name is null)
	    		-- temporary tables exclusions
		    and object_name(f.id, @dbid) not in 
					(select e.table_name
					   from #dbcc_exclusions e
					  where e.fault_type is null)
			-- temporary combo exclusions
		    and not exists (select 1 
				      from #dbcc_exclusions e
				     where e.table_name = 
						object_name(f.id, @dbid)
				       and e.fault_type = f.type_code
				       and e.fault_type is not null 
				       and e.table_name is not null) ) )

	/*
	** If the list of faults is empty skip this database
	*/
	if not exists (select 1 from #fault_info1) 
	begin
		drop table #fault_info1
		fetch cursor_dbcc_db_list into @dbid, @dbname
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	/* We are going to generate a report, set @rtrn_status to 0 */
	select @rtrn_status = 0

	/* Create an index on the #fault_info1 table */
	create nonclustered index i on #fault_info1 (objid, indid, partitionid)

	/* 
	** Did the user request fix recommendations?  If so, call
	** sp_dbcc_run_recommendations.
	*/
	if ((@display_recommendations is not null) 
		    and (@display_recommendations = 1))
	begin
		    print ''
		    exec sp_dbcc_run_recommendations 
				    @dbname, null, @opid, @objectname
		    print ''
	end

    	if (@report_type = 'long')
    	begin /* { */
	    /* create #fault_params for all faults we are reporting on */
	    select distinct fp.type_code, fp.faultid, fp.intvalue, 
		    fp.realvalue, fp.binaryvalue, 
		    fp.stringvalue, fp.datevalue
	      into #fault_params
	      from dbcc_fault_params fp, #fault_info1 f
	     where fp.dbid = @dbid 
	       and fp.opid = @opid 
	       and fp.faultid = f.faultid 
	     order by fp.faultid
    
	    declare cursor_dbcc_ptnids cursor  
		    for select distinct objid, indid, partitionid 
			  from #fault_info1 order by objid, indid, partitionid

	    declare cursor_dbcc_faults cursor  
		    for select  distinct type_code, intval, faultid, faultname
			from #fault_info1 
			where objid = @objid 
			  and indid = @indid
			  and partitionid = @partitionid
			order by type_code

	    declare cursor_fault_params cursor for 
		    select type_code, intvalue, binaryvalue, realvalue, 
			    stringvalue, datevalue
		    from #fault_params where faultid = @faultid

	    /*
	    ** There are three while loops in this block. 
	    ** The outer most loop prints fault report for each 
	    ** object in the #fault_info1 table.
	    */ 

	    open cursor_dbcc_ptnids
	    fetch cursor_dbcc_ptnids into @objid, @indid, @partitionid
	    select @sqlstatus2 = @@sqlstatus
	    while (@sqlstatus2 = 0) 
	    begin 	/* { */
		/*
		** If object_name is null, it indicates that the 
		** object was dropped after the checkstorage 
		** operation. Use the object_id in the reports.
		*/
		select @objname = isnull(object_name(@objid, @dbid),
				    convert(varchar(12), @objid))

		/*
		** 18487, "Generating 'Fault Report' for object %1!, indid %2!, partition id %3! in database %4!."
		*/
		exec sp_getmessage 18487, @msg output
		print @msg, @objname, @indid, @partitionid, @dbname
		print ""
    
		open cursor_dbcc_faults
		fetch cursor_dbcc_faults into @type_code1, 
				    @intval, @faultid, @faultname
		select @sqlstatus3 = @@sqlstatus
		while (@sqlstatus3 = 0) 
		begin 	/* { */
		    /*
		    ** This loop takes care of all faults recorded 
		    ** in the #fault_info1 table for the object @objid
		    */
		    select @desc1 = 
			    case @intval
			    when 0 then "Soft fault, possibly spurious"
			    when 1 then "Hard fault"
			    when 2 then "Soft fault verified as spurious"
			    when 3 then "Soft fault upgraded to Hard fault"
			    else	"Unknow Status"
			    end

		    select @desc2 = description 
		      from dbcc_types 
			    where type_code = @type_code1

		    print "Fault Type: %1! (%2!); %3!", 
				@type_code1, @faultname, @desc1
		    print "%1!", @desc2

		    open cursor_fault_params
		    fetch cursor_fault_params into @type_code2, 
				@intval, @binval, @realval, 
				@strval, @dateval
		    select @sqlstatus4 = @@sqlstatus
		    while (@sqlstatus4 = 0) 
		    begin	/* { */
			/*
			** This loop prints all the fault parameters
			** associated with the given faultid
			** Each fault param row contains only one of 
			** the param values as not null.
			*/
			select @desc2 = type_name 
			  from dbcc_types 
			 where type_code = @type_code2
			if (@intval is not null)
			begin
				print "%1!:  %2!", @desc2, @intval
			end
			else if (@realval is not null)
			begin
				print "%1!:  %2!", @desc2, @realval
			end
			else if (@strval is not null)
			begin
				print "%1!:  %2!", @desc2, @strval
			end
			else if (@dateval is not null)
			begin
				print "%1!:  %2!", @desc2, @dateval
			end

			if (@binval is null)
			begin
				goto next_fault_param
		        end

			/*
			** Now we know @binval is not null.
			** The binary value is stored in a platform
			** independent format, the code below is 
			** written such a way that it works
			** correctly on both little endian
			** and big endian platforms.
			*/

			/* 
			** Types for encoded text column identifier 
			** The column offset is a -ve number derived 
			** by subtracting 65536 from the value available
			** as an int.
			*/
			if (@type_code2 = 10002)
			begin	/* { */
			    select @pno = hextoint(bintostr(substring(@binval,1,4))),
				   @rno = hextoint(bintostr(substring(@binval,5,2))),
				   @col = hextoint(bintostr(substring(@binval,7,2)))
				   		- 65536
			    select @msg =  '%1!: %2!(page %3!, row %4!, column offset  %5!)'
			    print @msg, @desc2, @binval, @pno, @rno, @col
			end 	/* } encoded object id */

			/* Types for encoded object identifier */
			else if (@type_code2 in (10003, 10009))
			begin	/* { */
			    select @fobjid = hextoint(bintostr(substring(@binval,1,4))),
				   @fpartitionid = hextoint(bintostr(substring(@binval,5,4))),
				   @findid = hextoint(bintostr(substring(@binval,9,1)))
			    /* 
			    ** Print the object id only if it is different from 
			    ** the object we are reporting on.
			    */
			    if ((@objid != @fobjid) or (@indid != @findid)
			     or (@partitionid != @fpartitionid))
			    begin
				    select @msg =  '%1!: %2!(objid %3!, indid %4!, partitionid %5!)'
				    print @msg, @desc2, @binval, @fobjid, @findid, @fpartitionid
			    end
			end 	/* } encoded object id */

			/* Types for page headers */
			else if (@type_code2 in (10001, 10010, 10011, 10012))
			begin	/* { */
			    print "%1!:  %2!", @desc2, @binval

			    /*
			    ** We will now try to convert the
			    ** binary page header to a more
			    ** meaningful format. 
			    ** 
			    ** First convert the portion of the page
			    ** header that is similar in all formats.
			    */
			    select @pno = hextoint(bintostr(substring(@binval,1,4))),
				   @next_pno = hextoint(bintostr(substring(@binval,5,4))),
				   @prev_pno = hextoint(bintostr(substring(@binval,9,4))),
				   @ppartitionid = hextoint(bintostr(substring(@binval,13,4))),
				   @ts_lo = hextoint(bintostr(substring(@binval,17,4))),
				   @nextrno = hextoint(bintostr(substring(@binval,21,2))),
				   @level = hextoint(bintostr(substring(@binval,23,1))),
				   @pindid = hextoint(bintostr(substring(@binval,24,1))),
				   @ts_hi = hextoint(bintostr(substring(@binval,25,2))),
				   @freeoff = hextoint(bintostr(substring(@binval,27,2))),
				   @stat = hextoint(bintostr(substring(@binval,29,2))),
				   @minlen = hextoint(bintostr(substring(@binval,31,2)))
			     
			    select @ts_str = '0x' + substring(inttohex(@ts_hi),5,4) 
							    + inttohex(@ts_lo)
			     
			    select @stat_str = '0x' + substring(inttohex(@stat),5,4)

			    /* Print the page identification info */
			    select @msg =  'Header for %1!, next %2!, previous %3!, id = %4!:%5!'
			    print @msg, @pno, @next_pno, @prev_pno, @ppartitionid, @pindid
			    /* type_code 10001 is for APL page header */
			    if (@type_code2 = 10001)
			    begin	/* { */
				/* No page format specific fields in header */
				select @msg = ' time stamp = %1!, next row = %2!, level = %3!'
				print @msg, @ts_str, @nextrno, @level
				
				select @msg = ' free offset = %1!, minlen = %2!, status = %3!(%4!)'
				print @msg, @freeoff, @minlen, @stat, @stat_str
			    end	/* } */
			    /* type_code 10010 is for DOL data page header */
			    else if  (@type_code2 = 10010)
			    begin	/* { */
				/* 
				** Among common fields:
				** @level is spare1.
				** Get page format specific fields in header. 
				*/
				select @stat2 = hextoint(bintostr(substring(@binval,33,1))),
				       @priv_stat = hextoint(bintostr(substring(@binval,34,1))),
				       @psz = hextoint(bintostr(substring(@binval,35,1))),
				       @pvers = hextoint(bintostr(substring(@binval,36,1))),
				       @ncfs = hextoint(bintostr(substring(@binval,37,2))),
				       @ndeleted = hextoint(bintostr(substring(@binval,39,2))),
				       @insfs = hextoint(bintostr(substring(@binval,41,2)))

				select @stat2_str = '0x' + substring(inttohex(@stat2),5,4)
				select @priv_stat_str = '0x' + substring(inttohex(@priv_stat),5,4)
				/*
				** insfs can become -ve value.
				** If the computed value is 
				** >= 32768, reset it to a 
				** -ve number by subtracting 
				** 65536. This is because insfs
				** field is a 16 bit number
				*/
				if (@insfs >= 32768)
				begin
					select @insfs = @insfs - 65536
				end
				 
				select @msg = ' time stamp = %1!, next row = %2!, spare1 = %3!'
				print @msg, @ts_str, @nextrno, @level
				select @msg = ' free offset = %1!, minlen = %2!, status = %3!(%4!)'
				print @msg, @freeoff, @minlen, @stat, @stat_str, @stat2
				select @msg = ' status2 = %1!(%2!), private status = %3!(%4!), page size = %5!, version = %6!'
				print @msg, @stat2, @stat2_str, @priv_stat, @priv_stat_str, 
				      @psz, @pvers
				select @msg = ' non-contiguous free space (ncfs)  = %1!, deleted rows = %2!'
				print @msg, @ncfs, @ndeleted
				select @msg = ' insert free space = %1!'
				print @msg, @insfs
			    end	/* } */
			    /* type_code 10011 is for DOL index leaf page header */
			    else if (@type_code2 = 10011)
			    begin	/* { */
				/* 
				** For this format among the common fields:
				** @nextrno is number of entries(btl_numentries)
				** @minlen is btl_fixedlen 
				**
				** Get page format specific fields.
				** Among these fields:
				** @priv_stat is btl_leafstat 
				** @pvers is btl_version 
				*/
				select @stat2 = hextoint(bintostr(substring(@binval,33,1))),
				       @priv_stat = hextoint(bintostr(substring(@binval,34,1))),
				       @psz = hextoint(bintostr(substring(@binval,35,1))),
				       @pvers = hextoint(bintostr(substring(@binval,36,1))),
				       @numrid = hextoint(bintostr(substring(@binval,37,2))),
				       @ncfs = hextoint(bintostr(substring(@binval,39,2))),
				       @reserv = hextoint(bintostr(substring(@binval,41,4)))
				 
				select @stat2_str = '0x' + substring(inttohex(@stat2),5,4)
				select @priv_stat_str = '0x' + substring(inttohex(@priv_stat),5,4)
				 
				select @msg = ' time stamp = %1!, next row = %2!, level = %3!'
				print @msg, @ts_str, @nextrno, @level
				
				select @msg = ' free offset = %1!, fixed length = %2!, status = %3!(%4!)'
				print @msg, @freeoff, @minlen, @stat, @stat_str
				select @msg = ' status2 = %1!(%2!), leaf status = %3!(%4!), page size = %5!, version = %6!'
				print @msg, @stat2, @stat2_str, @priv_stat, @priv_stat_str, 
				      @psz, @pvers
				select @msg = ' undeleted rids = %1!, non-contiguous free space (ncfs) = %2!, reserved = %3!'
				print @msg, @numrid, @ncfs, @reserv
			    end	/* } */
			    /* type_code 10012 is for DOL index nonleaf page header */
			    else if (@type_code2 = 10012)
			    begin	/* { */
				/*
				** For this format among the common fields:
				** @next_pno is btn_reserved1 and 
				** @prev_pno is btn_reserved2 
				** @nextrno is btn_numentries
				** @minlen is btn_fixedlen
				**
				** Among extra fields:
				** @priv_stat is btn_nleafstat 
				** @pvers is btn_version 
				*/
				select @stat2 = hextoint(bintostr(substring(@binval,33,1))),
				       @priv_stat = hextoint(bintostr(substring(@binval,34,1))),
				       @psz = hextoint(bintostr(substring(@binval,35,1))),
				       @pvers = hextoint(bintostr(substring(@binval,36,1))),
				       @pgleft = hextoint(bintostr(substring(@binval,37,4))),
				       @reserv = hextoint(bintostr(substring(@binval,41,4)))
				 
				select @stat2_str = '0x' + substring(inttohex(@stat2),5,4)

				select @priv_stat_str = '0x' + substring(inttohex(@priv_stat),5,4)

				select @msg = ' time stamp = %1!, number of entries = %2!, level = %3!'
				print @msg, @ts_str, @nextrno, @level
				
				select @msg = ' free offset = %1!, fixed length = %2!, status = %3!(%4!)'
				print @msg, @freeoff, @minlen, @stat, @stat_str
				select @msg = ' status2 = %1!(%2!), non-leaf status = %3!(%4!), page size = %5!, version = %6!'
				print @msg, @stat2, @stat2_str, @priv_stat, @priv_stat_str, @psz, @pvers
				select @msg = ' leftmost child page = %1!, reserved = %2!'
				print @msg, @pgleft, @reserv
			    end	/* } */
			end	/* } page header */
next_fault_param:	
			fetch cursor_fault_params into @type_code2, 
				@intval, @binval, @realval, 
					@strval, @dateval
			select @sqlstatus4 = @@sqlstatus
		    end  /* } while (@sqlstatus4 = 0) */
		    print ""
		    close cursor_fault_params
		    fetch cursor_dbcc_faults into @type_code1, 
					@intval, @faultid, @faultname
		    select @sqlstatus3 = @@sqlstatus
		end  /* } while (@sqlstatus3 = 0) */
		print ""
		close cursor_dbcc_faults
		fetch cursor_dbcc_ptnids into @objid, @indid, @partitionid
		select @sqlstatus2 = @@sqlstatus
	    end  /* } while (@sqlstatus2 = 0) */

	    close cursor_dbcc_ptnids
	    deallocate cursor cursor_fault_params
	    deallocate cursor cursor_dbcc_faults
	    deallocate cursor cursor_dbcc_ptnids
	    drop table #fault_params
    	end	/* } if (@report_type = 'long') */
    	else	
    	begin	/* { generate report in short format */
	    /* Update intval in #fault_info1 with the page# where available */
	    update #fault_info1
		set intval = fp.intvalue
	       from #fault_info1 f, dbcc_fault_params fp
	      where f.faultid = fp.faultid
		and fp.opid = @opid
		and fp.dbid = @dbid
		and ((fp.type_code = 10000)
		     or  (fp.type_code = 10003
			  and not exists 
				(select fps.type_code
				   from dbcc_fault_params fps
				  where fps.faultid = fp.faultid
				    and fps.opid = fp.opid
				    and fps.dbid = fp.dbid
				    and fps.type_code = 10000)
			)
		     )
		
	    print ""
	    print "Database Name : %1!", @dbname
	    print ""
	    /*
	    ** If Object name is not valid, print object id
	    */
	    select tname = isnull(object_name(fi.objid, @dbid), 
			          convert(varchar(10), fi.objid)),
		   fi.indid, fi.partitionid, 
		   fault = convert(varchar, fi.type_code) 
				+ " (" + fi.faultname + ")",
		   fi.intval
	    into #faultreport
	    from #fault_info1 fi

	    exec sp_autoformat "#faultreport", 
		"'Table Name' = tname, 'Index' = indid,
		 'PartitionId' = partitionid, 'Fault Type' = fault,
		 'Page Number' = intval", NULL, 
		 "order by tname, indid, partitionid, fault, intval"
	    drop table #faultreport
    	end	/* } */
    	drop table #fault_info1
    	fetch cursor_dbcc_db_list into @dbid, @dbname
    	select @sqlstatus1 = @@sqlstatus
end  /* } */

close cursor_dbcc_db_list
deallocate cursor cursor_dbcc_db_list

drop table #dbcc_db_list

if (@rtrn_status != 0)
begin
	/*
	** 18491, "No %1! on dbcc operations available"
	*/
        raiserror 18491, @report_name
end
return (@rtrn_status)
go
grant execute on sp_dbcc_run_faultreport to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_differentialreport')
begin
	drop procedure sp_dbcc_run_differentialreport
end
go
print "Installing sp_dbcc_run_differentialreport"
go


/*
** Messages for "sp_dbcc_run_differentialreport"   
** 17260, "Can't run %1! from within a transaction."
** 18488, "Object name must be accompanied by database name."
** 18485, "date1 and date2 are same. No Report Generated."
** 18486, "Generating 'Differential Report' for object %1! in database %2!."
** 18491, "No %1! on DBCC operations available."
** 18476, "%1! is not a valid value for \'%2!\'."
*/
create procedure sp_dbcc_run_differentialreport 
( 
		@dbname  varchar(255) = null,
		@objectname  varchar(255) = null,
		@db_op varchar(30) = null,
		@date1  datetime = null,
		@date2  datetime = null
)
as
declare  @dbid smallint 		/* dbid of the database */
declare  @sqlstatus1 int, @sqlstatus2 int
declare  @value1 int, @value2  int 
declare  @objid int 
declare  @ret int 
declare  @objname varchar(255) 
declare	 @optype_for_checkstorage  int
declare	 @opid1  int, @opid2  int
declare	 @partid  smallint
declare	 @param_type  int
declare  @msg varchar(1024) 
declare  @title_printed int	/* did we print title before */
declare  @report_name varchar(30)	
declare  @rtrn_status int	/* 1 if no report was generated */
declare  @tempdate datetime	
declare  @dbname_isnull int
declare  @is_remote_tdb int

/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        exec sp_getmessage 17260, @msg output
        raiserror 17260 @msg, "sp_dbcc_differentialreport"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

if (@date1 is not null and @date2 is not null and (@date1 = @date2))
begin
	/*
	** 18485, "date1 and date2 are same. No Report Generated"
	*/
	raiserror 18485 
	return 1
end

select @report_name = "differential report"

create table  #dbcc_dblist(dbid  smallint, dbname  varchar(255))
declare cursor_dbcc_dblist cursor  
	for select  dbid, dbname from #dbcc_dblist 

/*
**  insert dbname and dbid for all valid databases in #dbcc_dblist
*/
insert #dbcc_dblist(dbid, dbname) 
	select d.dbid, d.name 
	from master..sysdatabases d
	where exists ( select * from dbcc_counters c 
				where c.dbid = d.dbid ) 

select @dbid = null

/*
** If the dbname was specified by the user, validate it 
*/
if (@dbname is not null)
begin
	select @dbname_isnull = 0

	/*
	** If the dbname is valid, delete other databases from
	** #dbcc_dblist. Otherwise return error,
	** because we are interested in only one database
	*/
    	exec  @ret = sp_validatedb  @dbname, @dbid output
    	if (@dbid is not null)
		delete from #dbcc_dblist where dbid != @dbid
	else
		return (1)
end
else
begin
	select @dbname_isnull = 1

	if (@objectname is not null)
	begin
		/*
		** 18488, "Object name must be accompanied by database name"
		*/
		raiserror 18488 
		return (1)
	end
end
 
/*
** Currently only valid db_op is checkstorage. 
** The code for checkstorage is 2.
*/
if (@db_op is not null and @db_op != 'checkstorage')
begin
	/*
	**  18476, "%1! is not a valid value for \'%2!\'."
	*/
	raiserror 18476, @db_op, 'DBCC operation'
	return 1
end 

select @optype_for_checkstorage = 2

select @rtrn_status = 1

/*
** select only those types that is found in dbcc_counters table 
** This is a an optimization.
*/
create table #temp_dbcc_types(type_code  int, type_name varchar(50))
insert #temp_dbcc_types(type_code, type_name)
	select distinct t.type_code, t.type_name
	from dbcc_types t, dbcc_counters c
	where t.type_code = c.type_code 

create table #dbcc_obj_list(objid int, objname varchar(255))
declare cursor_obj_list cursor for 
	select objid, objname from #dbcc_obj_list order by objname, objid

create table  #stat_info1(objid  int, indid smallint, partitionid int,
				type_code  int, 
				value1 real null, value2 real null)

create table  #stat_info2(objid  int, indid smallint, partitionid int,
				type_code  int, devid int, 
				value1 real null, value2  real null)

create table  #counter_sum_types(type int) 
declare cursor_counter_sum_types cursor for 
	select type from #counter_sum_types
insert #counter_sum_types(type) values(5000)
insert #counter_sum_types(type) values(5001)
insert #counter_sum_types(type) values(5006)
insert #counter_sum_types(type) values(5015)
insert #counter_sum_types(type) values(5020)
insert #counter_sum_types(type) values(5021)
insert #counter_sum_types(type) values(5022)
insert #counter_sum_types(type) values(5023)
insert #counter_sum_types(type) values(5024)

create table  #counter_max_types(type int) 
declare cursor_counter_max_types cursor for 
	select type from #counter_max_types
insert #counter_max_types(type) values(5007)
insert #counter_max_types(type) values(5008)
insert #counter_max_types(type) values(5009)

create table  #counter_reg_types(type int) 
declare cursor_counter_reg_types cursor for 
	select type from #counter_reg_types
insert #counter_reg_types(type) values(5002)
insert #counter_reg_types(type) values(5003)
insert #counter_reg_types(type) values(5004)
insert #counter_reg_types(type) values(5005)
insert #counter_reg_types(type) values(5010)
insert #counter_reg_types(type) values(5011)
insert #counter_reg_types(type) values(5012)
insert #counter_reg_types(type) values(5013)
insert #counter_reg_types(type) values(5014)
insert #counter_reg_types(type) values(5016)
insert #counter_reg_types(type) values(5017)
insert #counter_reg_types(type) values(5018)
insert #counter_reg_types(type) values(5019)

open cursor_dbcc_dblist
fetch cursor_dbcc_dblist into @dbid, @dbname
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0) 
begin
	/*
	** In SDC, if dbname is not specified, skip local tempdbs that
	** are only accessible from remote instances.
	*/
	if (@@clustermode = "shared disk cluster" and @dbname_isnull = 1)
	begin
		exec @is_remote_tdb = sp_check_remote_tempdb @dbid, 'skip'
		if (@is_remote_tdb = 1)
		begin
			print ""
			fetch cursor_dbcc_dblist into @dbid, @dbname
			select @sqlstatus1 = @@sqlstatus
			continue
		end
	end

	/*
	** If object_name() is null, it indicates that the object was
	** dropped after the checkstorage operation. Use the object id
	** in the reports.
	*/
	if (@objectname is not null)
	begin
		insert #dbcc_obj_list(objid, objname)
			select distinct id, 
				isnull(object_name(id, @dbid), 
					convert(varchar,id))
			from dbcc_counters
			where dbid = @dbid 
				and object_name(id, @dbid) = @objectname
	end
	else
	begin
		insert #dbcc_obj_list(objid, objname)
			select distinct id, 
				isnull(object_name(id, @dbid), 
					convert(varchar,id))
			from dbcc_counters where dbid = @dbid 
	end

	exec sp_getopid @dbname, @date1, @optype_for_checkstorage, 
					@opid1 output  
	/*
	** if @date2 is null, get the opid of the checkstorage operation
	** that was completed just before @opid1
	*/
	if (@date2 is null)
	begin
		select @tempdate = finish from dbcc_operation_log
		    where dbid = @dbid 
			and optype = @optype_for_checkstorage
			and opid = @opid1
		select @tempdate = dateadd(minute, -1, @tempdate)
	end
	exec sp_getopid @dbname, @tempdate, @optype_for_checkstorage, 
					@opid2 output

	if (@opid1 is null or @opid2 is null)
	begin
		fetch cursor_dbcc_dblist into @dbid, @dbname
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	insert #stat_info1(objid, indid, partitionid, type_code, value1, 
				value2) 
		select distinct c1.id, c1.indid, c1.partitionid,
				c1.type_code, sum(c1.value), sum(c2.value)
		from dbcc_counters c1, dbcc_counters c2, 
				#counter_sum_types st, #dbcc_obj_list ol
		where c1.opid = @opid1 and c2.opid = @opid2
			and c1.type_code = st.type and c1.dbid = @dbid 
			and c1.id = ol.objid 
			and c1.dbid = c2.dbid and c1.id = c2.id
			and c1.indid = c2.indid and c1.devid = c2.devid
			and c1.type_code = c2.type_code
			and c1.partitionid = c2.partitionid
		group by c1.opid, c1.dbid, c1.type_code, c1.id, c1.indid, 
			 c1.partitionid

	insert #stat_info1(objid, indid, partitionid, type_code, value1, 
				value2) 
		select distinct c1.id, c1.indid, c1.partitionid,
				c1.type_code, max(c1.value), max(c2.value)
		from dbcc_counters c1, dbcc_counters c2, 
				#counter_max_types st, #dbcc_obj_list ol
		where c1.opid = @opid1 and c2.opid = @opid2
			and c1.type_code = st.type and c1.dbid = @dbid 
			and c1.id = ol.objid 
			and c1.dbid = c2.dbid and c1.id = c2.id
			and c1.indid = c2.indid and c1.devid = c2.devid
			and c1.type_code = c2.type_code
			and c1.partitionid = c2.partitionid
		group by c1.opid, c1.dbid, c1.type_code, c1.id, c1.indid, 
			 c1.partitionid

	insert #stat_info2(objid, indid, partitionid, type_code, devid, 
					value1, value2) 
		select distinct c1.id, c1.indid, c1.partitionid, c1.type_code, 
				c1.devid, c1.value, c2.value
		from dbcc_counters c1, dbcc_counters c2, #dbcc_obj_list ol,
				master..sysdevices d, #counter_reg_types st
		where c1.opid = @opid1 and c2.opid = @opid2
			and c1.type_code = st.type and c1.dbid = @dbid 
			and c1.id = ol.objid 
			and c1.dbid = c2.dbid and c1.id = c2.id
			and c1.indid = c2.indid and c1.devid = c2.devid
			and c1.type_code = c2.type_code
			and c1.partitionid = c2.partitionid
			and ((d.status & 6) != 0)
			and d.vdevno = c1.devid


	delete from  #stat_info1 where value1 = value2 
	delete from  #stat_info2 where value1 = value2

	open cursor_obj_list
	fetch cursor_obj_list into @objid, @objname
	select  @sqlstatus2 = @@sqlstatus

	while (@sqlstatus2 = 0) 
	begin
	    select @title_printed = 0
	    if exists (select * from #stat_info1 where objid = @objid)
	    begin 
		/* 
		** 18486, "Generating 'Differential Report' for object %1! in database %2!."
		*/ 
		exec sp_getmessage 18486, @msg output 
		print @msg, @objname, @dbname 
		print "" 
		select "Parameter Name" = convert(varchar(50), t.type_name), 
			"Index Id" = convert(varchar(3), st1.indid),
			"Partition Id" = convert(varchar(10), st1.partitionid),
			"Value1" = convert(varchar(12), value1),
			"Value2" = convert(varchar(12), value2)
			from #stat_info1 st1, #temp_dbcc_types t
		where st1.objid = @objid and st1.type_code = t.type_code
		order by indid, partitionid

		/* we printed the title here */
	    	select @title_printed = 1
	    	print ""
	    end
	    if exists (select * from #stat_info2 where objid = @objid)
	    begin
		/* title was not printed before. Print here */
		if (@title_printed = 0)
		begin
			/* 
			** 18486, "Generating 'Differential Report' for object %1! in database %2!."
			*/ 
			exec sp_getmessage 18486, @msg output 
			print @msg, @objname, @dbname 
			print "" 
		end
	    	select   "Parameter Name" = convert(varchar(50), t.type_name),
			"Index Id" = convert(varchar(3), st2.indid),
			"Partition Id" = convert(varchar(10), st2.partitionid),
			"Value1" = convert(varchar(12), value1),
			"Value2" = convert(varchar(12), value2),
			"Dev_name" = d.name
		from #stat_info2 st2, #temp_dbcc_types t, 
				master..sysdevices d
		where st2.objid = @objid and st2.type_code = t.type_code
			  and ((d.status & 6) != 0) 
			  and d.vdevno = st2.devid
		order by indid, partitionid

	    	print ""
	    end
	    select @rtrn_status = 0
	    fetch cursor_obj_list into @objid, @objname
	    select  @sqlstatus2 = @@sqlstatus
	end

	truncate table #stat_info1
	truncate table #stat_info2
	close cursor_obj_list
	fetch cursor_dbcc_dblist into @dbid, @dbname
	select @sqlstatus1 = @@sqlstatus
end

close cursor_dbcc_dblist
deallocate cursor cursor_dbcc_dblist
drop table #dbcc_dblist
drop table #stat_info1
drop table #stat_info2

if (@rtrn_status != 0)
begin
	/*
	** 18491, "No %1! on DBCC operations available."
	*/
	raiserror 18491, @report_name
	return(1)
end

return (0)
go
grant execute on sp_dbcc_run_differentialreport to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_fullreport')
begin
	drop procedure sp_dbcc_run_fullreport
end
go
print "Installing sp_dbcc_run_fullreport"
go


/*
** Messages for "sp_dbcc_run_fullreport"    
** 17260, "Can't run %1! from within a transaction."
** 18488, "Object name must be accompanied by database name."
** 18491, "No %1! on DBCC operations available."
*/
create procedure sp_dbcc_run_fullreport 
( 
		@dbname  varchar(255) = null,
		@objectname  varchar(255) = null,
		@date  datetime = null
)
as
declare  @dbid smallint 		/* dbid of the database */
declare  @sqlstatus1 int 
declare  @ret int 
declare  @opid int 
declare  @optype_for_checkstorage int 
declare  @msg varchar(1024) 
declare  @rtrn_status int	/* 1 if no report was generated */
declare  @report_name varchar(30)  
declare  @dbname_isnull int
declare	 @is_remote_tdb int


/*
** Disallow this procedure within a transaction since it 
** creates temporary tables
*/
if @@trancount > 0
begin
        /*
        ** 17260, "Can't run %1! from within a transaction."
        */
        exec sp_getmessage 17260, @msg output
        raiserror 17260 @msg, "sp_dbcc_fullreport"
        return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1
set nocount on

create table  #dbcc_db_list(dbid  smallint, dbname  varchar(255))
declare cursor_dbcc_db_list cursor  
	for select  dbid, dbname from #dbcc_db_list 

select @report_name = "full report"

/*
**  insert dbname and dbid for all valid databases in #dbcc_db_list
*/
insert #dbcc_db_list(dbid, dbname) 
	select d.dbid, d.name 
	from master..sysdatabases d
	where exists ( select * from dbcc_counters c 
				where c.dbid = d.dbid ) 

select @dbid = null

/*
** If the dbname was specified by the user, validate it 
*/
if (@dbname is not null)
begin
	select @dbname_isnull = 0

	/*
	** If the dbname is valid, delete other databases from
	** #dbcc_db_list. Otherwise return error
	** because we are interested in only one database
	*/
    	exec  @ret = sp_validatedb  @dbname, @dbid output
    	if (@dbid is not null)
		delete from #dbcc_db_list where dbid != @dbid
	else
		return (1)
end
else
begin
	select @dbname_isnull = 1

	if (@objectname is not null)
	begin
		/*
		** 18488, "Object name must be accompanied by database name"
		*/
		exec sp_getmessage 18488, @msg output
		raiserror 18488 @msg
		return (1)
	end
end
 
create table #dbcc_obj_list(objid int)
declare cursor_obj_list cursor for 
	select distinct objid from #dbcc_obj_list

select @optype_for_checkstorage = 2

select @rtrn_status = 1
open cursor_dbcc_db_list
fetch cursor_dbcc_db_list into @dbid, @dbname
select @sqlstatus1 = @@sqlstatus
while (@sqlstatus1 = 0) 
begin
	/*
	** Get the opid for this database corresponding to the date 
	** specified. If none, skip this database
	*/
	exec sp_getopid @dbname, @date, @optype_for_checkstorage, 
					@opid output  
	if (@opid is null)
	begin
		fetch cursor_dbcc_db_list into @dbid, @dbname
		select @sqlstatus1 = @@sqlstatus
		continue
	end

	/*
	** In SDC, if dbname is not specified, skip local tempdbs that
	** are only accessible from remote instances.
	*/
	if (@@clustermode = "shared disk cluster" and @dbname_isnull = 1)
	begin
		exec @is_remote_tdb = sp_check_remote_tempdb @dbid, 'skip'
		if (@is_remote_tdb = 1)
		begin
			print ""
			fetch cursor_dbcc_db_list into @dbid, @dbname
			select @sqlstatus1 = @@sqlstatus
			continue
		end
	end
	/*
	** Prepare a list of objects on which report to be generated.
	** If the list is empty skip this database.
	*/
	if (@objectname is not null)
	begin
		insert #dbcc_obj_list(objid)
			select id
			from dbcc_counters
			where dbid = @dbid 
			and object_name(id, @dbid) = @objectname
			and opid = @opid
	end
	else
	begin
		insert #dbcc_obj_list(objid)
			select id
			from dbcc_counters 
			where dbid = @dbid and opid = @opid
	end

	if exists (select * from #dbcc_obj_list) 
	begin

	     execute sp_dbcc_run_summaryreport  @dbname
	     print ""
	     execute sp_dbcc_run_configreport  @dbname
	     print ""

             execute sp_dbcc_run_statisticsreport  @dbname, @objectname, @date
             print ""
             execute sp_dbcc_run_faultreport  'short', @dbname, @objectname, 
		@date
             print ""

	     select @rtrn_status = 0
	end

	fetch cursor_dbcc_db_list into @dbid, @dbname
	select @sqlstatus1 = @@sqlstatus
end

close cursor_dbcc_db_list
deallocate cursor cursor_obj_list
deallocate cursor cursor_dbcc_db_list
drop table #dbcc_obj_list
drop table #dbcc_db_list

if (@rtrn_status != 0)
begin
	/*
	** 18491, "No %1! on DBCC operations available"
	*/
	exec sp_getmessage 18491, @msg output
        raiserror 18491  @msg, @report_name
	return (1)
end

return (0)
go
grant execute on sp_dbcc_run_fullreport to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_patch_finishtime')
begin
	drop procedure sp_dbcc_run_patch_finishtime
end
go
print "Installing sp_dbcc_run_patch_finishtime"
go


/*
** This procedure provides the API for patching the finishtime of a checkstorage
** operation.
**
** Messages: 
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 17421, "No such database -- run sp_helpdb to list databases."
** 18480, "Database name is null. Specify a valid database name."
** 19021, Cannot patch the finish time of the checkstorage operation (opid '%1!', database '%2!'%3!). Check opid and retry.
** 19023, Finish time must be earlier than the current time and later than the start time ('%1!').
** 19024, Invalid operation type '%1!'. Valid types are 'checkstorage' and 'checkverify'.
*/
create procedure sp_dbcc_run_patch_finishtime 
( 
		@dbname  varchar(255),		/* dbname to patch */
		@opid	 smallint,		/* opid to patch */
		@optype	 varchar(30) = null,	/* type of operation 
						** 'checkstorage' or 
						** 'checkverify'. If this
						** isnot specified or null is
						** specified it will be assumed
						** to be 'checkstorage' by 
						** default.
						*/
		@seq	 smallint = null,	/* the sequence number for
						** a checkverify operation.
						*/
		@finishtime datetime = null	/* the value to be set for
						** finishtime 
						*/
)
as
declare  @dbid int 			/* dbid of the database */
declare  @dbo int                       /* is the owner of the database ? */
declare  @opcode int                    /* The opcode corresponding to optype */
declare	 @start datetime 
declare  @seqstr varchar(50)		/* string used to print the sequence in
					** error messages.
					*/
declare  @dummy int

/* validate dbname */
if (@dbname is null)
begin
	/* 18480, "Database name is null. Specify a valid database name." */
	raiserror 18480
	return (1)
end

/*
**  Verify the database name and get the @dbid
*/
select @dbid = dbid
        from master.dbo.sysdatabases
                where name = @dbname

/*
**  If @dbname not found, say so 
*/
if @dbid is NULL
begin
	/* 17421, "No such database -- run sp_helpdb to list databases." */
	raiserror 17421
	return (1)
end

exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname

/*
**  Only the Database Owner (DBO) or
**  Accounts with SA role can execute it.
*/
if ((@dbo != 1) and (charindex("sa_role", show_role()) < 1))
begin
	/*
	** Call proc_role() to generate the appropriate
	** audit record.
	*/
	select @dummy = proc_role("sa_role")
	/*
	** 17230, "You must be the System Administrator (SA) or
	** the Database Owner (dbo) to execute this procedure."
	*/
	raiserror 17230
	return (1)
end

/* validate the optype */
if (@optype is null) 
begin
	select @opcode = 2, @optype = 'checkstorage'
end
else if (lower(@optype) = 'checkstorage')
	select @opcode = 2
else if (lower(@optype) = 'checkverify')
	select @opcode = 3
else
begin
	/* 19024, Invalid operation type '%1!'. Valid types are 'checkstorage' and 'checkverify'. */
	raiserror 19024, @optype
	return (1)
end

if (@seq is null)
begin
	select @seqstr = ""
end
else
begin
	select @seqstr = ", seq '" + convert(varchar, @seq) + "'"
end


/* If finishtime is not specified use current time, else validate it. */
if (@finishtime is NULL)
begin
	select @finishtime = getdate()
end
else
begin
	/* 
	** The finishtime specified must be after start time and before 
	** current time.
	*/
	select @start = start from dbcc_operation_log 
	where dbid = @dbid and opid = @opid and optype = @opcode 
	  and finish is null and (@seq is null or seq = @seq)
	/* If start is null, we can't find the row to update */
	if (@start is null)
	begin
		/* 19021, Cannot patch the finish time of the checkstorage operation (opid '%1!', database '%2!'%3!). Check opid and retry.  */
		raiserror 19021, @opid, @dbname, @seqstr
		return (1)
	end

	if ((@finishtime > getdate()) or (@finishtime < @start)) 
	begin
		/* 19023, Finish time must be earlier than the current time and later than the start time ('%1!'). */
		raiserror 19023, @start
		return (1)
	end
end

update dbcc_operation_log
   set finish = @finishtime
 where dbid = @dbid
   and opid = @opid
   and optype = @opcode
   and finish is null
   and start <= @finishtime
   and (@seq is null or seq = @seq)

if ((@@error != 0) or (@@rowcount != 1))
begin
	/* 19021, Cannot patch the finish time of the checkstorage operation (opid '%1!', database '%2!'%3!). Check opid and retry.  */
	raiserror 19021, @opid, @dbname, @seqstr
	return 1
end

return 0
go
grant execute on sp_dbcc_run_patch_finishtime to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_dbcc_run_exclusions')
begin
	drop procedure sp_dbcc_run_exclusions
end
go
print "Installing sp_dbcc_run_exclusions"
go

/*
** This is an internal dbcc proc that is invoked by sp_dbcc_exclusions
** to do the real work using the appropriate dbcc database(dbccdb or dbccalt).
**
** Messages for "sp_dbcc_exclusions" 	  
**
** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
** 17284, "'%1!' is not a valid identifier."
** 17421, "No such database -- run sp_helpdb to list databases."
** 18635, "The database '%1!' with id %2! was configured as '%3!' in dbccdb database. Please delete the configuration and history using the command '%4!' and retry the sp_dbcc_updateconfig command."
** 18990, "You must be the System Administrator (SA) to execute this procedure with a NULL database name (dbname) parameter."
** 19004, "You must specify the operation(@op) to be performed for the specified exclusion list (@exclusion_list)."
** 19005, "You cannot specify a database name or an exclusion list for the 'listall' operation."
** 19006, "You must specify an exclusion list and its type for the '%1!' operation."
** 19007, "The specified operation '%1!' is invalid. Valid operations are 'add', 'drop', 'listall', and null."
** 19008, "Invalid exclusion list type '%1!' specified. Valid types are 'faults', 'tables', and 'combo'."
** 19009, "Ignoring invalid combo token '%1!'."
** 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."
** 19011, "Invalid fault type '%1!', ignoring token '%2!'."
** 19012, "Aborted dbcc exclusion list '%1!' operation due to error '%2!'."
** 19013, "Added %1! '%2!' dbcc exclusions for database %3!."
** 19014, "Dropped %1! '%2!' dbcc exclusions for database %3!."
** 19044, "Added %1! '%2!' default dbcc exclusions applicable for all databases." 
** 19045, "Dropped %1! '%2!' default dbcc exclusions."
**
*/
create procedure sp_dbcc_run_exclusions 
( 
		@dbname  varchar(255) = null,     /* dbname or null for all */
		@op	 varchar(30) = null,	 /* op 'add','drop', null 
						 ** or 'listall' to list 
						 ** exclusions for all 
						 ** databases including the 
						 ** default exclusions 
						 */
		@type    varchar(10) = null,     /* [faults]| tables| combo 
						 ** or null when op is null or 
						 ** 'listall' */
		@exclusion_list varchar(2000) = null /* comma separated list */
)
as
declare @dbid int, 			/* dbid of the database */
	@dbuid int,                     /* id of the owner of the database */
	@type_code int,
	@stringvalue varchar(255),
	@msg varchar(1024),
	@cmd varchar(1024),
	@error int,
	@rowcount int,
	@suid int,
	@comma  char(1), 
	@colon  char(1),
	@clist varchar(2000),
	@i	smallint,
	@i2	tinyint,
	@token varchar(300),
	@fault_type int,
	@table_name varchar(255),
	@sarole tinyint,
	@maxlen	int

if @@trancount = 0
begin
        set chained off
end
 
set transaction isolation level 1

set nocount on

if (@dbname is not NULL)
begin
	/* Verify the database name and get the @dbid and @dbuid */
	select @dbid = dbid, @dbuid = suid
		from master.dbo.sysdatabases
			where name = @dbname
	 
	/* If @dbname not found, say so */
	if @dbid is NULL
	begin
		/* 17421, "No such database -- run sp_helpdb to list databases." */
		raiserror 17421
		return (1)
	end
end
else
begin
	select @dbid = 0, @dbuid = 0
end

/*
**  Permission check:
**  if user has sa_role all operations are granted.
**  if dbname is not null and user is the dbo all permissions granted.
**  if dbname is null 
**	if op is null or 'listall' 
**		the user must own at least one database. 
**		for listall case we will restrict the listing to
**  		only the databases owned by the user. 
**	else permission not granted.
*/

select @suid = suser_id(), @sarole = charindex("sa_role", show_role())
if (@sarole <= 0)
begin
	if (@dbname is not null)
	begin
		if (@suid != @dbuid)
		begin
			/* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
			raiserror 17230 
			return (1)
		end
	end
	else
	begin
		if (@op in (null, 'listall'))
		begin
			/* Verify that user owns at least one database */
			if (not exists  (select 1 from master.dbo.sysdatabases
					 where suid = @suid))
			begin
				raiserror 17230 
				return (1)
			end
		end
		else
		begin
			/* 18990, "You must be the System Administrator (SA) to execute this procedure with a NULL database name (dbname) parameter." */
			raiserror 18990
			return(1)
		end
	end
end

/*
** Check the type of operation.
**	if op = listall
**		dbname must be null, list must be null
**		For this operation we will list the exclusions of all databases.
**	   op = null
**		list must be null
**		For this operation we will list the exclusions for the specified
** 		database or list the default exclusions if dbname is null.
**	   op = 'add' or 'drop' 
**		list must be non null, type must be non null
**		For this operation we will add or drop the specified exclusions
** 		for the specified database or for all databases 
**		if dbname is null.
**	else
**		Invalid operation.
*/
if (@op is null)
begin
	if (@exclusion_list is not null)
		/*
		** 19004, "You must specify the operation(@op) to be performed for the specified exclusion list (@exclusion_list)."
		*/
		raiserror 19004
end
else if (@op = 'listall')
begin
	if ((@dbname is not null) or  (@exclusion_list is not null))
		/* 19005, "You cannot specify a database name or an exclusion list for the 'listall' operation." */
		raiserror 19005

end
else if (@op in ('add', 'drop'))
begin
	if ((@exclusion_list is null) or (@type is null))
		/* 19006, "You must specify an exclusion list and its type for the '%1!' operation." */
		raiserror 19006, @op
end
else
	/* 19007, "The specified operation '%1!' is invalid. Valid operations are 'add', 'drop', 'listall', and null." */
	raiserror 19007

/* Abort in case of error */
if @@error != 0 
	return (1)

/* 
** Parse the type: 
** Type can be 'faults', 'tables' or 'combo' (1,2 and 3 respectively )
** combo indicates each entry in the exclusion list is a 
** table:fault combination.
** NOTE: for tables and combo the individual entries must be enclosed
** in single quotes. 
** If the table name is a quoted name then specify the same enclosed in
** double quotes. This is not guranteed to work though
** 
*/
if (@type is not null)
begin
	select @type_code = case when(@type = 'faults') then 1
				 when(@type = 'tables') then 2
				 when(@type = 'combo') 	then 3
				 else 0
			    end

	if (@type_code = 0)
	begin
		/* 19008, "Invalid exclusion list type '%1!' specified. Valid types are 'faults', 'tables', and 'combo'." */
		raiserror 19008, @type
		return(1)
	end
end
else
begin
	select @type_code = 0
end

/* Now we do the real work */
if (@op in ('add', 'drop'))
	goto add_drop

/* 
** List the exclusions as requested 
** If op is 'listall' list exclusions for all databases owned by user
** else (op is null) list for specified database only
** Restrict the listing to the specified type (if type is specified)
*/

select 
	case when dbid = 0 then "Default Exclusions" else db_name(dbid) end 
	as Scope,
	case type 
		when 1 then 'Fault'
		when 2 then 'Table'
		when 3 then 'Combo'
		else 'Unknown'
	end as 'Type',
	'Table' = isnull(table_name, ""),
	'Fault' = isnull(convert(varchar(10),fault_type), "")
from dbcc_exclusions
where 
	((dbid = @dbid) or 
	 (@op = 'listall' and ((@sarole > 0) or 
			       dbid in (select dbid 
					from master.dbo.sysdatabases 
					where suid = @suid))))

  and (@type_code = 0 or type = @type_code) -- if type restriction is specified
order by dbid, type, table_name, fault_type
	     
return (0)

add_drop:

/* Get the max length for table names.  */
select @maxlen = length from syscolumns
 where id = object_id("sysobjects")
   and name = "name"

begin transaction

/*
** If name of this database is not in the dbcc_config table
** add it here
** RESOLVE: may be we should put this into a common sproc and call that
** from both sp_dbcc_updateconfig and this proc.
*/
if ((@dbid != 0) and (@op = 'add') and 
    not exists (select * from dbcc_config 
		where dbid = @dbid and type_code = 6 and stringvalue = @dbname))
begin
	if exists (select * from dbcc_config where dbid = @dbid)
	begin
		declare @old_dbname	varchar(255)
		declare @cmd_name	varchar(40)
		
		rollback transaction

		/*
		** The database might have been dropped or renamed.
		** Ask the user to run sp_dbcc_deletedb <dbid>
		** to get rid of the history and later update the 
		** configuration.
		**
		** 18635, "The database '%1!' with id %2! was configured as 
		** '%3!' in dbccdb database. Please delete the configuration 
		** and history using the command '%4!' and retry the 
		** sp_dbcc_updateconfig command."
		*/
		exec sp_getmessage 18635, @msg output
		select @old_dbname = stringvalue from dbcc_config 
					where dbid = @dbid and type_code = 6
		select @cmd_name = 'sp_dbcc_deletedb  ' + 
					convert(varchar(10), @dbid)
		print @msg, @dbname, @dbid, @old_dbname, @cmd_name
		return (1)
	end
	/* 6 is for database name and 5 is for operation sequence number.  */
	insert into dbcc_config values(@dbid, 6, null, @dbname)
	insert into dbcc_config values(@dbid, 5, 0, null)
end


/* Initialize some variables */
select @rowcount = 0 , @comma = ",", @colon = ":", @clist = @exclusion_list

/* Initialize fault_type/table_name to null based on type_code */
if (@type_code = 1)
	select @table_name  = null
else if (@type_code = 2)
	select @fault_type  = null

/* Parse list and insert/delete */
while(1=1)
begin
	select @i = charindex(@comma, @clist)
	if (@i = 0) 
	begin
		select @token = @clist
	end
	else
	begin
		select @token = substring(@clist,1, @i -1)
	end

	select @token = ltrim(rtrim(@token))

	if (@type_code = 1)
	begin
		select @fault_type = convert(int,@token) 
	end
	else if (@type_code = 2)
	begin
		select @table_name = @token
	end
	else
	begin
		/* Combo case break into table_name and fault_type */
		select @i2 = charindex(@colon, @token)
		if (@i2 = 0)
		begin
			/* 19009, "Ignoring invalid combo token '%1!'." */
			exec sp_getmessage 19009, @msg output
			print @msg, @token
			goto next_token
		end

		select @table_name = rtrim(substring(@token , 1, @i2 -1)),
		       @fault_type = convert(int, 
					     substring(@token, @i2+1, 
						       char_length(@token)-@i2))
	end

	/* Check for error */
	if (@@error != 0) 
	begin
		/* 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."	*/
		exec sp_getmessage 19010, @msg output
		print @msg, @token
		goto next_token
	end

	/* 
	** Validate table type when type code is other than 2
	** fault_types are stored in dbcc_types  with type_code starting 
	** from 100000. Ignore invalid faults. 
	*/
	if ((@type_code != 2) and 
	    ((@fault_type is null) or 
	     not exists (select 1 from dbcc_types 
			where type_code = @fault_type
			  and type_code >= 100000  )))
	begin
		/* 19011, "Invalid fault type '%1!', ignoring token '%2!'." */
		exec sp_getmessage 19011, @msg output
		print @msg, @fault_type, @token
		goto next_token
	end

	/* 
	** Validate table name when type code is other than 1
	** We don't validate the existence table name here since it can be 
	** created later. Just check to make sure we have a valid 
	** identifier. Ignore invalid identifiers. 
	*/
	if ((@type_code != 1) and (valid_name(@table_name, @maxlen) = 0))
	begin
		/* 17284, "'%1!' is not a valid identifier." */
		exec sp_getmessage 17284, @msg output
		print @msg, @table_name

		/* 19010, "Error while parsing the exclusion list. Ignoring invalid token '%1!'."	*/
		exec sp_getmessage 19010, @msg output
		print @msg, @token
		goto next_token
	end

	if (@op = 'add')
	begin
		insert into dbcc_exclusions
			(dbid, type, table_name, fault_type) 
			values (@dbid, @type_code, @table_name, @fault_type)
	end
	else
	begin
		delete from dbcc_exclusions 
			where dbid = @dbid 
		  	and type = @type_code
			and table_name = @table_name
			and fault_type = @fault_type
	end
	/* Error processing */
	select @error = @@error, @rowcount = @rowcount + @@rowcount
	if (@error != 0)
	begin
		rollback tran
		/* 19012, "Aborted dbcc exclusion list '%1!' operation due to error '%2!'." */
		exec sp_getmessage 19012, @msg output
		print @msg, @op, @error
		return (1)
	end

next_token:

	/* Check if we are done */
	if (@i = 0) 
		break

	select @clist = substring(@clist, @i + 1,char_length(@clist)-@i)
end

commit transaction

/*
** 19013, "Added %1! '%2!' dbcc exclusions for database %3!."
** 19014, "Dropped %1! '%2!' dbcc exclusions for database %3!."
** 19044, "Added %1! '%2!' default dbcc exclusions applicable for all databases." 
** 19045, "Dropped %1! '%2!' default dbcc exclusions."
*/
if (@dbname is not null)
begin
	if (@op = 'add')
		exec sp_getmessage 19013, @msg output
	else
		exec sp_getmessage 19014, @msg output
	print @msg, @rowcount, @type, @dbname
end
else
begin
	if (@op = 'add')
		exec sp_getmessage 19044, @msg output
	else
		exec sp_getmessage 19045, @msg output
	print @msg, @rowcount, @type
end
return (0)
go
grant execute on sp_dbcc_run_exclusions to public
go
if (db_name() = "dbccalt")
begin
	dump tran dbccalt with truncate_only
end
go
set nocount off
set proc_return_status on
go

print 'Loading of dbccalt database is complete.'
go

declare @retval int
exec @retval = sp_version 'installdbccalt', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'end'
if (@retval != 0) select syb_quit()
go

