/***************************************************************************/
/*   ADMIN2.SQL - SQL Administrator INSTALL SCRIPT             */
/*                              FOR SQL SERVER VERSION 4.2x          */
/***************************************************************************/
/* latest revision - 3/16/93                                               */
/* Copyright (C) 1993 - Microsoft Corporation                              */
/***************************************************************************/
print ''
print 'Install SQL Administrator Scripts (SQL Server version 4.2x)'
print ''
go
/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

use master
go

/*******************************************************************************/
/*	    DROP ALL EXISTING PROCEDURE AND TABLES FIRST		       */
/*******************************************************************************/
if exists (select * from sysobjects where name = 'sp_MScheck_admin' and sysstat & 7 = 4)
begin
  drop procedure sp_MScheck_admin
end
if exists (select * from sysobjects where name = 'sp_MSdevice_list' and sysstat & 7 = 4)
begin
  drop procedure sp_MSdevice_list
end
if exists (select * from sysobjects where name = 'sp_MSnext_devnumber' and sysstat & 7 = 4)
begin
  drop procedure sp_MSnext_devnumber
end
if exists (select * from sysobjects where name = 'sp_MSnext_devnumber' and sysstat & 7 = 4)
begin
  drop procedure sp_MSnext_devnumber
end
if exists (select * from sysobjects where name = 'sp_MSdev_mirror' and sysstat & 7 = 4)
begin
  drop procedure sp_MSdev_mirror
end
if exists (select * from sysobjects where name = 'sp_MSdev_description' and sysstat & 7 = 4)
begin
	drop procedure sp_MSdev_description
end
if exists (select * from sysobjects where name = 'sp_MSdev_databases' and sysstat & 7 = 4)
begin
  drop procedure sp_MSdev_databases
end
if exists (select * from sysobjects where name = 'MSsystem_monitor')
begin
	drop table MSsystem_monitor
end
if exists (select * from sysobjects where name = 'MSlast_monitor')
begin
	drop table MSlast_monitor
end
if exists (select * from sysobjects where name = 'sp_MSsys_monitor' and sysstat & 7 = 4)
begin
	drop procedure sp_MSsys_monitor
end
if exists (select * from sysobjects where name = 'sp_MSdatabase_list' and sysstat & 7 = 4)
begin
	drop procedure sp_MSdatabase_list
end
if exists (select * from sysobjects where name = 'sp_MSdatabase_avail' and sysstat & 7 = 4)
begin
	drop procedure sp_MSdatabase_avail
end
if exists (select * from sysobjects where name = 'sp_MSbackup_now' and sysstat & 7 = 4)
begin
	drop procedure sp_MSbackup_now
end
if exists (select * from sysobjects where name = 'sp_MSbackup_schedule' and sysstat & 7 = 4)
begin
	drop procedure sp_MSbackup_schedule
end
if exists (select * from sysobjects where name = 'sp_MSuser_list' and sysstat & 7 = 4)
begin
	drop procedure sp_MSuser_list
end
if exists (select * from sysobjects where name = 'sp_MSmonitor' and sysstat & 7 = 4)
begin
	drop procedure sp_MSmonitor
end
if exists (select * from sysobjects where name = 'sp_MScmd_permissions' and sysstat & 7 = 4)
begin
  drop procedure sp_MScmd_permissions
end
if exists (select * from sysobjects where name = 'sp_MSuser_cmd_permissions' and sysstat & 7 = 4)
begin
  drop procedure sp_MSuser_cmd_permissions
end
if exists (select * from sysobjects where name = 'sp_MSsegment_list' and sysstat & 7 = 4)
begin
  drop procedure sp_MSsegment_list
end
if exists (select * from sysobjects where name = 'sp_MSsegment_devices' and sysstat & 7 = 4)
begin
  drop procedure sp_MSsegment_devices
end
if exists (select * from sysobjects where name = 'sp_MSseg_properties' and sysstat & 7 = 4)
begin
  drop procedure sp_MSseg_properties
end
if exists (select * from sysobjects where name = 'sp_MSseg_tables' and sysstat & 7 = 4)
begin
  drop procedure sp_MSseg_tables
end
if exists (select * from sysobjects where sysstat & 7 = 4 and name = 'sp_MSdb_options_bits')
begin
  drop procedure sp_MSdb_options_bits
end
if exists (select * from sysobjects where sysstat & 7 = 4 and name = 'sp_MSdb_devices_segments')
begin
  drop procedure sp_MSdb_devices_segments
end
if exists (select * from sysobjects where sysstat & 7 = 4 and name = 'sp_MSdb_space')
begin
  drop procedure sp_MSdb_space
end
if exists (select * from sysobjects where sysstat & 7 = 4 and name = 'sp_MSdb_options')
begin
  drop procedure sp_MSdb_options
end
if exists (select * from sysobjects where sysstat & 7 = 4 and name = 'sp_MSdb_devices')
begin
  drop procedure sp_MSdb_devices
end
if exists (select * from sysobjects where sysstat & 7 = 4 and name = 'sp_MSdb_properties')
begin
  drop procedure sp_MSdb_properties
end
if exists (select * from sysobjects where name = 'sp_MSthread_list' and sysstat & 7 = 4)
begin
  drop procedure sp_MSthread_list
end
if exists (select * from sysobjects where name = 'sp_MShelpdb' and sysstat & 7 = 4)
begin
  drop procedure sp_MShelpdb
end
if exists (select * from sysobjects where name = 'sp_MShelpremotelogin' and sysstat & 7 = 4)
begin
  drop procedure sp_MShelpremotelogin
end
if exists (select * from sysobjects where name = 'sp_MShelpserver' and sysstat & 7 = 4)
begin
  drop procedure sp_MShelpserver
end
if exists (select * from sysobjects where name = 'sp_MSAdmin_version' and sysstat & 7 = 4)
begin
  drop procedure sp_MSAdmin_version
end
go

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

/****************************************************************************/
/* This stored procedure is an updated version designed to tell users with  */
/* old .exe's to upgrade to the newer ones.                                 */
/****************************************************************************/


print ''
print 'Creating sp_MScheck_admin'
print ''
go
create procedure sp_MScheck_admin as

/* Create a temporary table to store the missing objects */

set nocount on

create table #spmissing
(name varchar(30),
 type varchar(10)
)

/* */
/* Check for the stored procedures first */
/* */

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdevice_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdevice_list', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdev_description' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdev_description', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdev_databases' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdev_databases', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSsys_monitor' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSsys_monitor', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSnext_devnumber' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSnext_devnumber', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdatabase_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdatabase_list', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdatabase_avail' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdatabase_avail', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSbackup_now' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSbackup_now', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSuser_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSuser_list', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSmonitor' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSmonitor', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_devices' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_devices', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_options' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_options', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_properties' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_properties', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_space' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_space', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSbackup_schedule' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSbackup_schedule', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MScmd_permissions' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MScmd_permissions', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSuser_cmd_permissions' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MScmd_permissions', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_options_bits' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_options_bits', 'procedure')
end

/* */
/* these are 4.2 only */
/* */

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdev_mirror' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdev_mirror', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_devices_segments' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_devices_segments', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSsegment_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSsegment_list', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSsegment_devices' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSsegment_devices', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSseg_properties' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSseg_properties', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSseg_tables' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSseg_tables', 'procedure')
end
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSthread_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSthread_list', 'procedure')
end

/* */
/* Check for the tables next */
/* */

if not exists (select * from master.dbo.sysobjects where name = 'MSscheduled_backups')
begin
  insert into #spmissing values('MSschedule_backups','table')
end
if not exists (select * from master.dbo.sysobjects where name = 'MSscheduled_backups_log')
begin
  insert into #spmissing values('MSschedule_backups_log','table')
end
if not exists (select * from master.dbo.sysobjects where name = 'MSsystem_monitor')
begin
  insert into #spmissing values('MSsystem_monitor','table')
end
if not exists (select * from master.dbo.sysobjects where name = 'MSlast_monitor')
begin
  insert into #spmissing values('MSlast_monitor','table')
end

set nocount off

print 'The SQL Administrator scripts on this server are for a later executable than you are running.  It is recommended that you upgrade your executable.'
select * from #spmissing
go


/****************************************************************************/
/* This stored procedure will get the name, type, size, location, device number, space
   available, and mirror status for all the devices in the system	    */
/****************************************************************************/
go
print ''
print 'Creating sp_MSdevice_list'
print ''
go
create procedure sp_MSdevice_list as

/* Create a temporary table where we can store all the necessary information */

set nocount on

create table #spdevtab
(name varchar(30) null,
 cntrltype varchar(10) null,
 devtype varchar(20) null,
 devsize float null,
 devsizeout varchar(20) null,
 spaceused float null,
 freespaceout varchar(20) null,
 mirror varchar(10) null,
 cntrlnum int null
)

declare @alloc float
select @alloc = convert(float,low) from master.dbo.spt_values where type = 'E' and number = 1

/* Insert the name, size, and space used for the database devices that have databases on them */

insert into #spdevtab (name, devsize, devsizeout, spaceused, mirror,cntrlnum)
select d.name,round((((convert(float,d.high) - convert(float,d.low)) * @alloc) / 1048576.0),0),
ltrim(str(round((((convert(float,d.high) - convert(float,d.low)) * @alloc)/1048576.0),0), 8,0)) + ' MB',
spaceused = (select round(sum((convert(float,usg.size) * @alloc) / 1048576.0),0) from master..sysusages usg
   where d.low <= usg.size + usg.vstart - 1 and d.high >= usg.size + usg.vstart - 1),
 'none',d.low/16777216
from master.dbo.sysdevices d
where d.cntrltype = 0 and exists (select * from master..sysusages usg
   where d.low <= usg.size + usg.vstart - 1 and d.high >= usg.size + usg.vstart - 1)


/* Insert the name, size for the database devices that are clean */

insert into #spdevtab (name, devsize, devsizeout, spaceused, mirror,cntrlnum)
select d.name,round((((convert(float,d.high) - convert(float,d.low)) * @alloc) / 1048576.0),0),
ltrim(str(round((((convert(float,d.high) - convert(float,d.low)) * @alloc)/1048576.0),0), 8,0)) + ' MB', 0, 'none',
d.low/16777216
from master.dbo.sysdevices d
where d.cntrltype = 0 and
d.name not in (select name from #spdevtab)

/* Insert the name for the database dump devices */

insert #spdevtab (name, devsizeout, freespaceout, mirror)
select d.name, '*******','*****', '*******'
from master.dbo.sysdevices d where
d.name not in (select name from #spdevtab)

/* Calculate the free space each disk device */

update #spdevtab set #spdevtab.freespaceout = convert(varchar(10),(convert(int,(#spdevtab.devsize - #spdevtab.spaceused)))) + ' MB'
		 from master.dbo.sysdevices d, #spdevtab where d.cntrltype = 0 and #spdevtab.name = d.name

/* set tape's to 0 length, this is a temporary fix to account for server requiring
   tape length, will change later */
update #spdevtab set #spdevtab.devsize = 0.0
		 from master.dbo.sysdevices d, #spdevtab where d.cntrltype = 5 and #spdevtab.name = d.name

/*  Now figure out what kind of controller type it is.

**  cntrltype = 0	special
**	        2	disk
**	      3-4	floppy
**	      5		tape
*/

update #spdevtab set #spdevtab.cntrltype = 'd' from master.dbo.sysdevices d, #spdevtab
		 where d.cntrltype = 2 and #spdevtab.name = d.name

update #spdevtab set #spdevtab.cntrltype = 'f' from master.dbo.sysdevices d, #spdevtab
		 where d.cntrltype between 3 and 4 and #spdevtab.name = d.name

update #spdevtab set #spdevtab.cntrltype = 't' from master.dbo.sysdevices d, #spdevtab
		 where d.cntrltype = 5 and #spdevtab.name = d.name

update #spdevtab set #spdevtab.cntrltype = 'UNKNOWN' from master.dbo.sysdevices d, #spdevtab
		 where d.cntrltype > 8 and #spdevtab.name = d.name

/* Get floppy device capacities which is listed in master.dbo.sysdevices.high in number of 62k blocks. */

if exists (select * from master.dbo.sysdevices d, #spdevtab
	   where d.cntrltype between 3 and 4 and d.high > 0 and #spdevtab.name = d.name)
begin
  update #spdevtab set devsizeout = ltrim(str(((d.high * 63488.0) / 1000000.0), 5,1)) + ' MB' from master.dbo.sysdevices d, #spdevtab
		   where d.cntrltype between 3 and 4 and d.high > 0 and #spdevtab.name = d.name
end

/* Get tape device capacities which is listed in master.dbo.sysdevices.high in number of 62k blocks. (skip decimal places) */

if exists (select * from master.dbo.sysdevices d, #spdevtab
	   where d.cntrltype = 5 and d.high > 0 and #spdevtab.name = d.name)
begin
/* Tempoary fix until server doesn't care about tape size */
/*  update #spdevtab set devsizeout =  ltrim(str(((d.high * 63488) / 1000000), 5,1)) + ' MB' from master.dbo.sysdevices d, #spdevtab
		   where d.cntrltype = 5 and d.high > 0 and #spdevtab.name = d.name */
  update #spdevtab set devsizeout =  '*******' from master.dbo.sysdevices d, #spdevtab
		   where d.cntrltype = 5 and d.high > 0 and #spdevtab.name = d.name
end

/* Now get the dump devices by turning the status bits into english. 0x10 = dump device. */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	   where v.type = 'V' and v.number > -1 and d.status & v.number = 16 and #spdevtab.name = d.name)
begin
  update #spdevtab set devtype = substring(v.name,1,4)
	 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	 where v.type = 'V' and v.number > -1 and d.status & v.number = 16 and #spdevtab.name = d.name
end

/* Now get the physical disks by turning the status bits into english. 0x02 is a physical disk. */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	   where v.type = 'V' and v.number > -1 and d.status & v.number = 2 and #spdevtab.name = d.name)
begin
  update #spdevtab set devtype = substring(v.name,charindex(' ',v.name)+ 1,4) from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	 where v.type = 'V' and v.number > - 1 and d.status & v.number = 2 and #spdevtab.name = d.name
end

/* Now get the default disk by turning the status bits into english. 0x01 is a default disk. */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	   where v.type = 'V' and v.number > -1 and d.status & v.number = 1 and #spdevtab.name = d.name)
begin
   update #spdevtab set devtype = 'dflt' from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	  where v.type = 'V' and v.number > -1 and d.status & v.number = 1 and #spdevtab.name = d.name
end

/* Now get the logical disks by turning the status bits into english. 0x04 is a logical disk. */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	   where v.type = 'V' and v.number > -1 and d.status & v.number = 4 and #spdevtab.name = d.name)
begin
  update #spdevtab set devtype = v.name from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	 where v.type = 'V' and v.number > -1 and d.status & v.number = 4 and #spdevtab.name = d.name
end

/* Now get the devices that are mirrored. The mirror status bits are >= 32. */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	   where v.type = 'V' and v.number > -1 and d.status >= 32 and #spdevtab.name = d.name)
begin
   if exists (select * from master.dbo.sysdevices d, #spdevtab
	      where d.status & 512 = 512 and #spdevtab.name = d.name)
   begin
     update #spdevtab set mirror = 'enabled' from master.dbo.sysdevices d
	    where d.status & 512 = 512 and #spdevtab.name = d.name
   end

   if exists (select * from master.dbo.sysdevices d, #spdevtab
	      where d.status & 512 != 512 and d.status >= 32 and #spdevtab.name = d.name)
   begin
     update #spdevtab set mirror = 'disabled' from master.dbo.sysdevices d
	    where d.status & 512 != 512 and d.status >= 32 and #spdevtab.name = d.name
   end
end

/* get control numbers > 127 to be positive */
update #spdevtab set #spdevtab.cntrlnum = 256+cntrlnum
  from master.dbo.sysdevices d where (#spdevtab.cntrlnum < 0 and d.cntrltype = 0) and (#spdevtab.name = d.name)
/* set rest to 0 */
update #spdevtab set #spdevtab.cntrlnum = 0
  from master.dbo.sysdevices d where d.cntrltype != 0 and #spdevtab.name = d.name

/* */
/* Final select statement to output the information */
/* */

set nocount off

select device_name = d.name, device_type = #spdevtab.cntrltype + #spdevtab.devtype,
       device_size = #spdevtab.devsizeout, location = substring(d.phyname, 1, 46),
       device_number = #spdevtab.cntrlnum, freespace = #spdevtab.freespaceout, mirror_stat = #spdevtab.mirror
from master.dbo.sysdevices d, #spdevtab
where d.name = #spdevtab.name
order by device_name, device_type
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/


/****************************************************************************/
/* This stored procedure will get the next available device number          */
/****************************************************************************/

print ''
print 'Creating sp_MSnext_devnumber'
print ''
go
create procedure sp_MSnext_devnumber as
declare @nextdev smallint, @maxdev smallint
select @nextdev = 1
select @maxdev = 255		/* devno is the high byte in sysdevices.low so should never change */

while @nextdev != @maxdev /* This is one max device number allowed */
begin
  if exists(select name from master.dbo.sysdevices where convert(smallint, convert(binary(1), (low / 16777216))) = @nextdev)
	select @nextdev = @nextdev + 1
  else
  begin
	select @nextdev
	return
  end
end
raiserror 70000 'No available device numbers'
go

/****************************************************************************/
/* This stored procedure gets the mirror status of a particular device.  This */
/* is only valid on the 4.2.1 server.  It is called by sp_MSdev_description */
/****************************************************************************/


print ''
print 'Creating sp_MSdev_mirror'
print ''
go
create procedure sp_MSdev_mirror
@devname varchar(30), @description varchar(255)  /* device and current description */
as

/*  TCD 8/2/91 See if the device exists. */

if not exists (select * from master.dbo.sysdevices
			 where name = @devname)
begin
	raiserror 70001 'Device doesn''t exist. The ''sa'' must have dropped the device since you last displayed the listing.'
	return
end


/* */
/* See if disk is mirrored.  Status bits >= 0x20 (32) */
/* */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v
	   where v.type = 'V' and v.number > -1 and d.status >= 32 and d.name = @devname)
begin

/*  Check to see if mirror in enabled. Status bits 0x200 (512). */

   if exists (select * from master.dbo.sysdevices d where d.status & 512 = 512 and d.name = @devname)
   begin
       select @description = @description + ', mirror enabled'
   end

/* Check to see if mirror is disabled.	*/

   if exists (select * from master.dbo.sysdevices d
       where d.status & 512 != 512 and d.status >= 32 and d.name = @devname)
   begin
       select @description = @description + ', mirror disabled'
   end

/* Get the name of the device it's mirrored on */

   select @description = @description + ', mirror on: ' + (select d.mirrorname
			 from master.dbo.sysdevices d where d.name = @devname)

end

/*
**  Check to see if there is a mirrorname entry but mirroring not enabled.
**  If so, then one side of the mirror is off-line.
*/

else if exists (select * from master.dbo.sysdevices d
		where d.name = @devname and d.mirrorname != null)
begin

	/* Figure out which side of the mirror is disabled. */
	/* If 0x100 is on, then phyname is disabled and mirrorname is enabled. */

  if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v
	     where v.type = 'V' and v.number > -1 and d.status & v.number = 256
	     and d.name = @devname)
  begin
       select @description = @description + ', only device: ' +
			      (select d.mirrorname + 'of mirror is enabled -- device: '
			      + d.phyname + ' is disabled'
			      from master.dbo.sysdevices d, master.dbo.spt_values v
			      where v.type = 'V' and v.number > -1 and d.status & v.number = 256
			      and d.name = @devname)
  end
  else if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v
	     where v.type = 'V' and v.number > -1 and d.status & v.number = 64
		   and d.name = @devname)
  begin
       select @description = @description + ', only device: ' +
			     (select d.mirrorname + 'of mirror is enabled -- device: '
			      + d.phyname + ' is disabled'
			      from master.dbo.sysdevices d, master.dbo.sysdevices e, master.dbo.spt_values v
			      where v.type = 'V' and v.number > -1 and d.status & v.number = 64 and
			      d.name = @devname and e.status & 256 != 256
			      and e.name = @devname)
  end
end

/* Check for 'serial writes' for mirrored disks. Status bits = 0x20 */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v
	   where v.type = 'V' and v.number > -1 and d.status & v.number = 32
	   and d.name = @devname)
begin
       select @description = @description + ', serial mirror writes'
end

/* Check for 'reads mirrored' for mirrored disks. Status bits = 0x80 */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v
		where v.type = 'V' and v.number > -1 and d.status & v.number = 128
	   and d.name = @devname)
begin
       select @description = @description + ', reads mirrored'
end

/* Output the final description */

set nocount off
select @description
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/


/****************************************************************************/
/* This stored procedure will get the description of characteristics for
   a particular device							    */
/****************************************************************************/

print ''
print 'Creating sp_MSdev_description'
print ''
go
create procedure sp_MSdev_description
@devname varchar(30)		  /* device to check out */
as

/*  See if the device exists. */

if not exists (select * from master.dbo.sysdevices
			 where name = @devname)
begin
	raiserror 70001 'Device doesn''t exist. The ''sa'' must have dropped the device since you last displayed the listing.'
	return
end

/* */
/* Create a temporary table where we can build up a translation of the device status bits. */
/* */

declare @description varchar(250)

set nocount on

/*
**  Now figure out what kind of controller type it is.	The type are
**  COMPLETELY platform dependent.
**  cntrltype = 0	special
**	        2	disk
**	      3-4	floppy
**	      5		tape
*/

if exists (select * from master.dbo.sysdevices d
	   where d.cntrltype = 0 and d.name = @devname)
begin
	   select @description = 'database/log device'
end

/* */
/* See if disk is a default disk. Status of 0x01 is a default disk. */
/* */

if exists (select * from master.dbo.sysdevices d, master.dbo.spt_values v
	   where v.type = 'V' and v.number > -1 and d.status & v.number = 1
	   and d.name = @devname)
begin
    select @description = @description + ', default disk'
end

if exists (select * from master.dbo.sysdevices d
		where d.cntrltype between 3 and 4 and d.name = @devname)
begin
	   select @description = 'floppy dump'
end

if exists (select * from master.dbo.sysdevices d
	   where d.cntrltype = 5 and d.name = @devname and d.status & 8 = 0)
begin
	   select @description = 'tape dump-no skip label'
end

if exists (select * from master.dbo.sysdevices d
	   where d.cntrltype = 5 and d.name = @devname and d.status & 8 = 8)
begin
	   select @description = 'tape dump-skip label'
end

exec sp_MSdev_mirror @devname, @description
go

/****************************************************************************/
/* This stored procedure will get the name, size, and owner of all the databases
   that use a particular device for data only, log only, data and log	    */
/****************************************************************************/
print ''
print 'Creating sp_MSdev_databases'
print ''
go
create procedure sp_MSdev_databases
@devname varchar(30)		  /* device to check out */
as


/*  TCD 8/2/91 See if the device exists. */

if not exists (select * from master.dbo.sysdevices
			 where name = @devname)
begin
	raiserror 70001 'Device doesn''t exist. The ''sa'' must have dropped the device since you last displayed the listing.'
	return
end

/* Databases that use the device for data only */
declare @low float
select @low = convert(float,a.low) from spt_values a where a.type = 'E' and a.number = 1
select name = db_name(usg.dbid),
size = ltrim(convert(varchar(10),round(sum((convert(float,usg.size)) * @low) / 1048576,0))) + ' MB',
usage = b.name, owner = suser_name(suid)
from sysdatabases d, sysusages usg, sysdevices v, spt_values b
where d.dbid = usg.dbid and v.low <= size + vstart
      and v.high >= size + vstart - 1
      and v.status & 2 = 2
      and v.name = @devname
      and b.type = 'S'
      and usg.segmap & 7 = b.number
group by usg.dbid, suser_name(suid), b.name
order by 1
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/************************************************************************/
/* This section to create all the tables necessary for scheduled
   backup and system monitoring                                         */
/************************************************************************/

print ''
print 'Creating MSscheduled_backups, if table already exists, ignore error message.'
print ''
go
create table MSscheduled_backups
(
Event_id		int		Not Null,	/* Unique identifier */
Database_name		varchar(30)	Not Null,	/* Name of database to be dumped */
Database_owner		varchar(30)	Not Null,	/* Name of the database owner */
Database_dump		varchar(30)	Null,		/* Database dump device */
Log_dump		varchar(30)	Null,		/* Log dump device */
Day			tinyint 	Not Null,	/* Day data is to be dumped */
Frequency		tinyint 	Not Null,	/* How often data is to be dumped */
Start_time		char(5) 	Not Null,	/* Time data should be dumped */
Enabled 		bit		Not Null,	/* 0 = disabled 1 = enabled */
Last_dump		datetime	Null,		/* Last dump that took place */
In_progress		bit		Not Null	/* 1 = backup in progress */
)
go
create unique index sched_idx on MSscheduled_backups (Event_id)
go

/* alter existing table to add Stat column */
if patindex('%Windows%',@@version) > 0
   or @@microsoftversion & 0x00FFFFFF >= 0x00050000
begin

if not exists (select name from syscolumns where name = 'Stat' and id = (select id from sysobjects where name = 'MSscheduled_backups'))
begin
   alter table MSscheduled_backups add Stat tinyint null
end

if not exists (select name from syscolumns where name = 'email_name' and id = (select id from sysobjects where name = 'MSscheduled_backups'))
begin
   alter table MSscheduled_backups add attempts tinyint null, email_name varchar(60) null
end
end
go

print 'Ignore errors on unknown column Stat if upgrading OS/2 SQL Server'
go

if patindex('%Windows%',@@version) > 0
   or @@microsoftversion & 0x00FFFFFF >= 0x00050000
begin
/* change existing table entries to indicate append of tape dump in stat column rather than */
/* in dump device name */
/* no append */
update MSscheduled_backups set Stat = 1 where (Database_dump not like '%+' or Database_dump = NULL) and (Log_dump not like '%+' or Log_dump = NULL) and Stat = NULL
/* append database dump */
update MSscheduled_backups set Stat = 2 where Database_dump like '%+' and (Log_dump not like '%+' or Log_dump = null) and Stat = NULL
/* append log dump */
update MSscheduled_backups set Stat = 3 where Log_dump like '%+' and (Database_dump not like '%+' or Database_dump = null) and Stat = NULL
/* append both */
update MSscheduled_backups set Stat = 4 where Database_dump like '%+' and Log_dump like '%+' and Stat = NULL
/* change '+'s to space */
update MSscheduled_backups set Database_dump = stuff(Database_dump,charindex('+',Database_dump),1,' ') where Stat != 1 and Database_dump like '%+'
/* trim out extra space */
update MSscheduled_backups set Database_dump = rtrim(Database_dump) where Stat != 1
/* change '+'s to space */
update MSscheduled_backups set Log_dump = stuff(Log_dump,charindex('+',Log_dump),1,' ') where Stat != 1  and Log_dump like '%+'
/* trim out extra space */
update MSscheduled_backups set Log_dump = rtrim(Log_dump) where Stat != 1
end
go


/* */
/* Create the scheduled backup log table */
/* */

print ''
print 'Creating MSscheduled_backups_log, if table already exists ignore error message.'
print ''
go
create table MSscheduled_backups_log
(
Event_ID		int		Not Null,	/* Event ID from schedule table */
Database_name		varchar(30)	Not Null,	/* Name of database to be dumped */
Actual_start_time	datetime	Not Null,	/* Actual time the dump started */
Actual_end_time 	datetime	Null,		/* Actual time the dump completed */
Status			bit		Not Null,	/* 1 = SUCCESS 0 = FAILED */
Message 		varchar(255)	Null		/* Error message upon failure. */
)
go

/* */
/* Create the MSsystem_monitor table */
/* */

print ''
print 'Creating MSsystem_monitor'
print ''
go
create table MSsystem_monitor
(
Monitor_time		datetime	Not Null,	/* Time the monitor data was taken */
CPU_busy_start		int		Not Null,	/* # secs CPU busy since server started */
CPU_busy_last		int		Not Null,	/* # seconds CPU was busy since last monitor */
CPU_busy_pct		tinyint 	Not Null,	/* Percent CPU was busy since server started */
IO_busy_start		int		Not Null,	/* # seconds IO was busy since server started */
IO_busy_last		int		Not Null,	/* # seconds IO was busy since last monitor */
IO_busy_pct		tinyint 	Not Null,	/* Percent IO was busy since server started */
Idle_start		int		Not Null,	/* # secs server idle since server started */
Idle_last		int		Not Null,	/* # seconds server was idle since last monitor */
Idle_pct		tinyint 	Not Null,	/* Percent server was idle since server started */
Pkts_sent_start 	int		Not Null,	/* # packets sent since server started */
Pkts_sent_last		int		Not Null,	/* # packets sent since last monitor */
Pkts_received_start	int		Not Null,	/* # packets received since server started */
Pkts_received_last	int		Not Null,	/* # packets received since last monitor */
Pkt_errors_start	int		Not Null,	/* # packet errors since server started */
Pkt_errors_last 	int		Not Null,	/* # packet errors since last montor */
Total_reads_start	int		Not Null,	/* # reads since server started */
Total_reads_last	int		Not Null,	/* # reads since last monitor */
Total_writes_start	int		Not Null,	/* # writes since server started */
Total_writes_last	int		Not Null,	/* # write since last  monitor */
Total_errors_start	int		Not Null,	/* # errors ince server started */
Total_errors_last	int		Not Null,	/* # errors since last monitor */
Connections_start	int		Not Null,	/* # connections since server started */
Connections_last	int		Not Null,	/* # connections since last monitor */
)
go

/* */
/* Create the MSlast_monitor table */
/* */

print ''
print 'Creating MSlast_montior'
print ''
go
create table MSlast_monitor
(
Last_time	   datetime	   Not Null,	   /* Time of last monitor */
CPU_busy	   int		   Not Null,	   /* # seconds CPU was busy since last monitor */
IO_busy 	   int		   Not Null,	   /* # seconds IO was busy since last monitor */
Idle		   int		   Not Null,	   /* # seconds server was idle since last monitor */
Pkts_sent	   int		   Not Null,	   /* # packets sent since last monitor */
Pkts_received	   int		   Not Null,	   /* # packets received since last monitor */
Pkt_errors	   int		   Not Null,	   /* # packet errors since last montor */
Total_reads	   int		   Not Null,	   /* # reads since last monitor */
Total_writes	   int		   Not Null,	   /* # write since last  monitor */
Total_errors	   int		   Not Null,	   /* # errors since last monitor */
Connections	   int		   Not Null,	   /* # connections since last monitor */
)
go

insert into MSlast_monitor
select getdate(), @@cpu_busy, @@io_busy, @@idle, @@pack_sent, @@pack_received,
       @@packet_errors, @@total_read, @@total_write, @@total_errors, @@connections
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/



/***************************************************************************/
/* This stored procedure performs the monitoring of the system.  It is a
   variation of the sp_monitor stored procedure on in the output and storage
   of data								   */
/***************************************************************************/


print ''
print 'Creating sp_MSsys_monitor'
print ''
go
create procedure sp_MSsys_monitor as

/* */
/*  Declare variables to be used to hold current monitor values. */
/* */

declare @now 		datetime
declare @cpu_busy 	int
declare @io_busy	int
declare @idle		int
declare @pack_received	int
declare @pack_sent	int
declare @pack_errors	int
declare @connections	int
declare @total_read	int
declare @total_write	int
declare @total_errors	int

declare @oldcpu_busy 	int	/* used to see if SQL Server has been rebooted */
declare @interval	int
declare @mspertick	int	/* milliseconds per tick */

/* */
/*  Set @mspertick.  This is just used to make the numbers easier to handle
    and avoid overflow. */
/* */

select @mspertick = convert(int, @@timeticks / 1000.0)

/*  Get current monitor values. */

begin transaction
select
	@now = getdate(),
	@cpu_busy = @@cpu_busy,
	@io_busy = @@io_busy,
	@idle = @@idle,
	@pack_received = @@pack_received,
	@pack_sent = @@pack_sent,
	@connections = @@connections,
	@pack_errors = @@packet_errors,
	@total_read = @@total_read,
	@total_write = @@total_write,
	@total_errors = @@total_errors

/*
**  Check to see if SQL Server has been rebooted.  If it has then the
**  value of @@cpu_busy will be less than the value of master.dbo.spt_monitor.cpu_busy.
**  If it has update master.dbo.spt_monitor.
*/

select @oldcpu_busy = CPU_busy from MSlast_monitor
if @oldcpu_busy > @cpu_busy
begin
   update MSlast_monitor set
	  Last_time = @now,
	  CPU_busy = @cpu_busy,
	  IO_busy = @io_busy,
	  Idle = @idle,
	  Pkts_received = @pack_received,
	  Pkts_sent = @pack_sent,
	  Connections = @connections,
	  Pkt_errors = @pack_errors,
	  Total_reads = @total_read,
	  Total_writes = @total_write,
	  Total_errors = @total_errors
end

/* */
/*  Insert a row in the monitor table to reflect the current values since the
    server was started. */
/* */

set nocount on

insert into MSsystem_monitor
select @now, (@cpu_busy * @mspertick) / 1000, 0, 0, (@io_busy * @mspertick) / 1000,
0, 0, (@idle * @mspertick) / 1000, 0,0, @pack_sent, 0, @pack_received, 0,
@pack_errors,0, @total_read, 0, @total_write, 0, @total_errors, 0, @connections, 0

/* */
/*  Now update the system monitor table with the difference between this monitor
    and the last monitor taken */
/* */

select @interval = datediff(ss, Last_time, @now)
from MSlast_monitor

update MSsystem_monitor
    set CPU_busy_last = ((@cpu_busy - CPU_busy) * @mspertick) / 1000,
	CPU_busy_pct = ((((@cpu_busy - CPU_busy) * @mspertick) / 1000) * 100) / @interval,
	IO_busy_last = ((@io_busy - IO_busy) * @mspertick) / 1000,
	IO_busy_pct =  ((((@io_busy - IO_busy) * @mspertick) / 1000) * 100) / @interval,
	Idle_last =  ((@idle - Idle) * @mspertick) / 1000,
	Idle_pct = ((((@idle - Idle) * @mspertick) / 1000) * 100) / @interval,
	Pkts_received_last = @pack_received - Pkts_received,
	Pkts_sent_last = @pack_sent - Pkts_sent,
	Pkt_errors_last = @pack_errors - Pkt_errors,
	Total_reads_last = @total_read - Total_reads,
	Total_writes_last = @total_write - Total_writes,
	Total_errors_last = @total_errors - Total_errors,
	Connections_last = @connections - Connections
from MSlast_monitor
where Monitor_time = @now

/* */
/* Now update the MSlast_monitor table for next round */
/* */

update MSlast_monitor set
	Last_time = @now,
	CPU_busy = @cpu_busy,
	IO_busy = @io_busy,
	Idle = @idle,
	Pkts_received = @pack_received,
	Pkts_sent = @pack_sent,
	Connections = @connections,
	Pkt_errors = @pack_errors,
	Total_reads = @total_read,
	Total_writes = @total_write,
	Total_errors = @total_errors

set nocount off


commit transaction

return
go


/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/


/****************************************************************************/
/* This stored procedure will get the name, size, owner, space available,
   and create date of all the databases in the system */
/****************************************************************************/

print ''
print 'Creating sp_MSdatabase_list'
print ''
go
create procedure sp_MSdatabase_list
as

select distinct name = d.name,
		db_size = substring( convert( varchar(10), round( convert(
                  float,v.low * (select sum(convert(float,u.size)) from master.dbo.sysusages u where dbid = d.dbid)
      ) / 1048576.0, 0 ) ) + 'MB', 1, 8 ),
		owner = suser_name(suid),
      spaceavail = '         ',
		created = convert(char(11), d.crdate)
from master.dbo.sysdatabases d, master.dbo.spt_values v
       where v.type = 'E' and v.number = 1
order by d.name
go


/****************************************************************************/
/* This stored procedure will get the space available for all the databases
   in the system */
/****************************************************************************/
print ''
print 'Creating sp_MSdatabase_avail'
print ''
go
create procedure sp_MSdatabase_avail
as

declare @db_size float, @reserved float, @unused float, @low float
declare @spaceavail float
set nocount on

declare @pagesize int
select @pagesize = low from master..spt_values where number = 1 and type = 'E'
select @db_size =  ((sum((convert(float,size)) * @pagesize) / 1048576.0) * 1024.0)
		   from master.dbo.sysusages
		   where dbid = db_id()

/* */
/* reserved: sum(reserved) where indid in (0, 1, 255) */
/* */

select @low = convert(float,d.low) from master.dbo.spt_values d where d.number = 1 and d.type = 'E'

select @reserved = sum(convert(float,reserved)) from sysindexes where indid in (0, 1, 255)
select @reserved = (@reserved * @low)/1024.0

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

select @unused = sum(convert(float,reserved)) - sum(convert(float,used)) from sysindexes where indid in (0, 1, 255)
select @unused = (@unused * @low)/1024.0

set nocount off
select @spaceavail = (convert(int,round(@db_size,0)) - convert(int,round((@reserved - @unused),0)))

if(@spaceavail < 0)
    select space_avail = '0KB'
else
    select space_avail = convert(varchar(10),convert(int,round(@spaceavail,0)))+'KB'

go
/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/


/****************************************************************************/
/* This stored procedure will get all the databases that need to be dumped
   at the time of inquiry for a Windows NT SQL Server */
/****************************************************************************/
if exists (select * from sysobjects where name = 'sp_MSbackup_now' and sysstat & 7 = 4)
begin
	drop procedure sp_MSbackup_now
end
go

print''
print 'Ignore error message if installing on a OS/2 SQL Server'
print''
print ''
print 'Creating sp_MSbackup_now'
print ''
go
create procedure sp_MSbackup_now as
declare @now datetime, @firstday smallint, @dayofweek smallint, @hour smallint, @minute smallint,
@elapsed int, @monthyear varchar(30)

set nocount on

/* */
/* Get the current date and time. */
/* Parse out the relevant parts of the date */
/* */

select @now = getdate()
if (@@langid=0)
    select @firstday=7
else
    select @firstday = datefirst from master..syslanguages where langid=@@langid

select @dayofweek = (datepart(weekday,@now)+@firstday)%7
select @monthyear = substring(convert(varchar(12),getdate()),1,12)

/* */
/* Create a temporary table that holds data on what needs to be dumped */
/* */

create table #spdumptab
(
id	       int	       Not Null,       /* Unique identifier */
name	       varchar(30)     Not Null,       /* Name of database to be dumped */
owner	       varchar(30)     Not Null,       /* Name of the database owner */
ddump	       varchar(30)     Null,	       /* Database dump device */
ldump	       varchar(30)     Null,	       /* Log dump device */
datacntrltype  smallint        Null,           /* Control type */
logcntrltype   smallint        Null,           /* Control type */
status         tinyint         Null,           /* extra dump parameters */
trys           tinyint         Null,           /* number of attempts to dump */
emailname      varchar(60)     Null,           /* email recipient(s) for notification */
dumptime       varchar(32)     Not Null,       /* scheduled event time */
day            tinyint         Not Null,       /* day of week for dump */
freq           tinyint         Not Null        /* frequency of dump */
)

/* */
/* Check all the databases that are dumped daily, weekly, and biweekly */
/* Note: The dump can only occur if the start time(HH:MM) is > the last dump <= now */
/* */

insert into #spdumptab
select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL,Stat,attempts,email_name,
    @monthyear+Start_time,Day,Frequency
from MSscheduled_backups
where Enabled = 1					     /* Dump turned on */
      and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)		     /* Dump today or Daily */
      and Frequency <= 14				     /* Freq daily, weekly, or biweekly */
      and datediff(day, Last_dump, @now) >= convert(smallint,Frequency)	     /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= convert(smallint,Frequency)*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= convert(smallint,Frequency)*24*60    /* Freq time has elapsed */

/* */
/* Check all the databases that are dumped monthly */
/* Note: First we get this week number, then do the same criteria as the
	 Daily, weekly, bi-weekly dump.
	 The dump can only occur if the start time(HH:MM) is > the last dump <= now */
/* */

declare @rundate datetime, @weekno smallint     /* Get this week number */
select @rundate = @now
select @weekno = 1
while datepart(month,dateadd(day,-7,@rundate)) = datepart(month,@now)
begin
    select @weekno = @weekno + 1
    select @rundate = dateadd(day,-7,@rundate)
end

insert into #spdumptab
select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL,Stat,attempts,email_name,
@monthyear+Start_time,Day,Frequency from MSscheduled_backups
where Enabled = 1					     /* Dump turned on */
      and (convert(smallint,Day) = @dayofweek)	             /* Dump today */
      and Frequency >= 31				     /* Freq monthly */
      and (convert(smallint,Frequency) - 22) >= @weekno	     /* Week of month */
      and datediff(day, Last_dump, @now) >= 22		     /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= 22*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= 22*24*60    /* Freq time has elapsed */


update #spdumptab set datacntrltype = (select distinct cntrltype from master..sysdevices s where
#spdumptab.ddump = s.name)

update #spdumptab set logcntrltype = (select distinct cntrltype from master..sysdevices s where
#spdumptab.ldump = s.name)


set nocount off

/* */
/* Output the values to the daemon */
/* */

select id = id, name = name, owner = owner, ddump = ddump, ldump = ldump,
dcntrl = datacntrltype, lcntrl = logcntrltype, stat = status, attempt=trys,
email=emailname,dumptime=convert(varchar(32),convert(datetime,dumptime)),day,freq from #spdumptab
order by (convert(datetime,dumptime))
go


/****************************************************************************/
/* This stored procedure will get all the databases that need to be dumped
   at the time of inquiry for an OS/2 SQL Server*/
/****************************************************************************/
if (patindex('%Windows%',@@version) = 0
         and @@microsoftversion & 0x00FFFFFF < 0x00050000) and
   exists (select * from sysobjects where name = 'sp_MSbackup_now' and sysstat & 7 = 4)
begin
        print 'OS/2 server, dropping NT version of sp_MSbackup_now'
	drop procedure sp_MSbackup_now
end
go

print''
print 'Ignore error message if installing on a Windows NT SQL Server'
print''
print ''
print 'Creating sp_MSbackup_now'
print ''
go

create procedure sp_MSbackup_now as
declare @now datetime, @firstday smallint, @dayofweek smallint, @hour smallint, @minute smallint,
@elapsed int, @monthyear varchar(30)

set nocount on

/* */
/* Get the current date and time. */
/* Parse out the relevant parts of the date */
/* */

select @now = getdate()
if (@@langid=0)
    select @firstday=7
else
    select @firstday = datefirst from master..syslanguages where langid=@@langid

select @dayofweek = (datepart(weekday,@now)+@firstday)%7
select @monthyear = substring(convert(varchar(12),getdate()),1,12)

/* */
/* Create a temporary table that holds data on what needs to be dumped */
/* */

create table #spdumptab
(
id	       int	       Not Null,       /* Unique identifier */
name	       varchar(30)     Not Null,       /* Name of database to be dumped */
owner	       varchar(30)     Not Null,       /* Name of the database owner */
ddump	       varchar(30)     Null,	       /* Database dump device */
ldump	       varchar(30)     Null,	       /* Log dump device */
datacntrltype  smallint        Null,           /* Control type */
logcntrltype   smallint        Null,           /* Control type */
)

/* */
/* Check all the databases that are dumped daily, weekly, and biweekly */
/* Note: The dump can only occur if the start time(HH:MM) is > the last dump <= now */
/* */

insert into #spdumptab
select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL
from MSscheduled_backups
where Enabled = 1					     /* Dump turned on */
      and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)		     /* Dump today or Daily */
      and Frequency <= 14				     /* Freq daily, weekly, or biweekly */
      and datediff(day, Last_dump, @now) >= convert(smallint,Frequency)	     /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= convert(smallint,Frequency)*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= convert(smallint,Frequency)*24*60    /* Freq time has elapsed */

/* */
/* Check all the databases that are dumped monthly */
/* Note: First we get this week number, then do the same criteria as the
	 Daily, weekly, bi-weekly dump.
	 The dump can only occur if the start time(HH:MM) is > the last dump <= now */
/* */

declare @rundate datetime, @weekno smallint     /* Get this week number */
select @rundate = @now
select @weekno = 1
while datepart(month,dateadd(day,-7,@rundate)) = datepart(month,@now)
begin
    select @weekno = @weekno + 1
    select @rundate = dateadd(day,-7,@rundate)
end

insert into #spdumptab
select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL
from MSscheduled_backups
where Enabled = 1					     /* Dump turned on */
      and (convert(smallint,Day) = @dayofweek)				     /* Dump today */
      and Frequency >= 31				     /* Freq monthly */
      and (convert(smallint,Frequency) - 22) >= @weekno			     /* Week of month */
      and datediff(day, Last_dump, @now) >= 22		     /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= 22*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= 22*24*60    /* Freq time has elapsed */


update #spdumptab set datacntrltype = (select cntrltype from master..sysdevices s where
#spdumptab.ddump = s.name)

update #spdumptab set logcntrltype = (select cntrltype from master..sysdevices s where
#spdumptab.ldump = s.name)


set nocount off

/* */
/* Output the values to the daemon */
/* */

select id = id, name = name, owner = owner, ddump = ddump, ldump = ldump,
dcntrl = datacntrltype, lcntrl = logcntrltype from #spdumptab
go



/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*
** This stored procedure returns the current scheduling for backups, it will
** get the database name, day (in chars), frequency (in chars), start time,
** and enabled status (in chars) from MSscheduled_backups.
*/


print ''
print 'Creating sp_MSbackup_schedule'
print ''
go
create procedure sp_MSbackup_schedule as

/* Create a tempory table to hold the results */

create table #spbacksch
(id int,
 dataname char (30) not null,
 day char (9) null,
 frequency char (9) null,
 start_time char(10) not null,
 enabled char (8) null )

set nocount on

/* Fill the temporary table with the id, database name, and start time in the schedule table */

insert into #spbacksch select d.Event_id,d.Database_name,NULL,NULL,convert(char,d.Start_time),NULL from MSscheduled_backups d

/* Now set the day of week to it's character equivalent from a numeric value */

update #spbacksch set day = '*********' from MSscheduled_backups d
                where d.Day = 0 and d.Event_id = id
update #spbacksch set day = 'Sunday' from MSscheduled_backups d
		where d.Day = 1 and d.Event_id = id
update #spbacksch set day = 'Monday' from MSscheduled_backups d
		where d.Day = 2 and d.Event_id = id
update #spbacksch set day = 'Tuesday' from MSscheduled_backups d
		where d.Day = 3 and d.Event_id = id
update #spbacksch set day = 'Wednesday' from MSscheduled_backups d
		where d.Day = 4 and d.Event_id = id
update #spbacksch set day = 'Thursday' from MSscheduled_backups d
		where d.Day = 5 and d.Event_id = id
update #spbacksch set day = 'Friday' from MSscheduled_backups d
		where d.Day = 6 and d.Event_id = id
update #spbacksch set day = 'Saturday' from MSscheduled_backups d
		where d.Day = 7 and d.Event_id = id

/* Now set the frequency to it's character equivalent from a numeric value */

update #spbacksch set frequency = 'Daily' from MSscheduled_backups d
                where d.Frequency = 1 and d.Event_id = id
update #spbacksch set frequency = 'Weekly' from MSscheduled_backups d
                where d.Frequency = 7 and d.Event_id = id
update #spbacksch set frequency = 'Bi-Weekly' from MSscheduled_backups d
                where d.Frequency = 14 and d.Event_id = id
update #spbacksch set frequency = 'Monthly - Week 1' from MSscheduled_backups d
                where d.Frequency = 31 and d.Event_id = id
update #spbacksch set frequency = 'Monthly - Week 2' from MSscheduled_backups d
                where d.Frequency = 32 and d.Event_id = id
update #spbacksch set frequency = 'Monthly - Week 3' from MSscheduled_backups d
                where d.Frequency = 33 and d.Event_id = id
update #spbacksch set frequency = 'Monthly - Week 4' from MSscheduled_backups d
                where d.Frequency = 34 and d.Event_id = id

/* Now set the enabled status to it's character equivalent from a numeric value */

update #spbacksch set enabled = 'No' from MSscheduled_backups d
                where d.Enabled = 0 and d.Event_id = id
update #spbacksch set enabled = 'Yes' from MSscheduled_backups d
                where d.Enabled = 1 and d.Event_id = id

set nocount off

/* select the data from the temporary table to give the schedule in a human understandable form */

select dataname, day, frequency, start_time, enabled, convert(char,id) from #spbacksch
               order by dataname,id

go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/



/****************************************************************************/
/* This stored proc will get the login id, user name, alias, and group for
	all the users in a database for the MDI list box		    */
/****************************************************************************/
print ''	/* print this or script hangs with some servers */

print ''
print 'Creating sp_MSuser_list'
print ''
go
create procedure sp_MSuser_list
as

create table #spusers	   /* Create temporary table for storage. */
(
 login_id varchar(30) null,
 user_name varchar(30) null,
 alias_name varchar(30) null,
 group_name varchar(30) null
)

set nocount on

/* */
/* Fill the temp table with system and user names */
/* */

insert into #spusers
select suser_name(suid), user_name(uid), '', '' from sysusers
where uid < 16383 and uid > 0

/* */
/* Get the aliases first */
/* */

insert into #spusers
select suser_name(sysalternates.suid),'', sysusers.name, ''
from sysusers, sysalternates, #spusers
where sysalternates.altsuid = suser_id(login_id)
and sysusers.suid = suser_id(login_id)

/* */
/* Get the group names */
/* */

update #spusers set group_name = g.name from sysusers u, sysusers g, #spusers
where u.suid = suser_id(#spusers.login_id) and
      u.uid = user_id(#spusers.user_name) and
      u.gid *= g.uid and
      u.uid <= 16383 and u.uid > 0

set nocount off

select * from #spusers
go

/****************************************************************************/
/* This stored procedure will get the needed values for the statistics
** monitoring dialog.  This also updates the system monitor in the same
** fashion as sp_monitor, thereby mimicing what sp_monitor does.
*/
/****************************************************************************/

print ''
print 'Creating sp_MSmonitor'
print ''
go
create procedure sp_MSmonitor
as

set nocount on

/*
**  Declare variables to be used to hold current monitor values.
*/
declare @now 		datetime
declare @cpu_busy 	int
declare @io_busy	int
declare @idle		int
declare @pack_received	int
declare @pack_sent	int
declare @pack_errors	int
declare @connections	int
declare @total_read	int
declare @total_write	int
declare @total_errors	int

declare @oldcpu_busy 	int	/* used to see if SQL Server has been rebooted */
declare @interval	int
declare @mspertick	int	/* milliseconds per tick */

/*
**  Set @mspertick.  This is just used to make the numbers easier to handle
**  and avoid overflow.
*/
select @mspertick = convert(int, @@timeticks / 1000.0)

/*
**  Get current monitor values.
*/
begin transaction
select
	@now = getdate(),
	@cpu_busy = @@cpu_busy,
	@io_busy = @@io_busy,
	@idle = @@idle,
	@pack_received = @@pack_received,
	@pack_sent = @@pack_sent,
	@connections = @@connections,
	@pack_errors = @@packet_errors,
	@total_read = @@total_read,
	@total_write = @@total_write,
	@total_errors = @@total_errors

/*
**  Check to see if SQL Server has been rebooted.  If it has then the
**  value of @@cpu_busy will be less than the value of master..spt_monitor.cpu_busy.
**  If it has update master..spt_monitor.
*/
select @oldcpu_busy = cpu_busy
	from master..spt_monitor
if @oldcpu_busy > @cpu_busy
begin
	update master..spt_monitor
		set
			lastrun = @now,
			cpu_busy = @cpu_busy,
			io_busy = @io_busy,
			idle = @idle,
			pack_received = @pack_received,
			pack_sent = @pack_sent,
			connections = @connections,
			pack_errors = @pack_errors,
			total_read = @total_read,
			total_write = @total_write,
			total_errors = @total_errors
end

/*
**  Now print out old and new monitor values.
*/
select @interval = datediff(ss, lastrun, @now) from master..spt_monitor s

select last_run = convert(char(30),lastrun),seconds = convert(char(30),@interval)
    from master..spt_monitor s

select
	CpuBusy = convert(varchar(30),'CPU Busy (ms)') + ','
                + convert(varchar(18),((@cpu_busy * @mspertick) / 1000)) + ','
		+ convert(varchar(18),((@cpu_busy - cpu_busy)* @mspertick) / 1000) + ','
		+ convert(varchar(18), ((((@cpu_busy - cpu_busy)* @mspertick) / 1000) * 100) / @interval),
	IOBusy = convert(varchar(30),'IO Busy (ms)') + ','
                + convert(varchar(18),((@io_busy * @mspertick) / 1000)) + ','
		+ convert(varchar(18),(((@io_busy - io_busy)* @mspertick) / 1000)) + ','
		+ convert(varchar(18), ((((@io_busy - io_busy)* @mspertick) / 1000) * 100) / @interval),
	Idle =   convert(varchar(30),'Idle Time (ms)') + ','
                + convert(varchar(18),((@idle * @mspertick) / 1000)) + ','
		+ convert(varchar(18),(((@idle - idle)* @mspertick) / 1000)) + ','
		+ convert(varchar(18),((((@idle - idle)* @mspertick) / 1000) * 100) / @interval),
	PacketsReceived = convert(varchar(30),'Packets Received') + ','
                + convert(varchar(18), @pack_received) + ','
		+ convert(varchar(18), @pack_received - pack_received),
        PacketsSent = convert(varchar(30),'Packets Sent') + ','
		+ convert(varchar(18), @pack_sent) + ','
                + convert(varchar(18), @pack_sent - pack_sent),
	PacketErrors = convert(varchar(30),'Packet Errors') + ','
                + convert(varchar(18), @pack_errors) + ','
                + convert(varchar(18), @pack_errors - pack_errors),
	TotalRead = convert(varchar(30),'Total Read') + ','
                + convert(varchar(18), @total_read) + ','
		+ convert(varchar(18), @total_read - total_read),
        TotalWrite = convert(varchar(30),'Total Write') + ','
                + convert(varchar(18), @total_write) + ','
		+ convert(varchar(18), @total_write - total_write),
	TotalErrors = convert(varchar(30),'Total Errors') + ','
                + convert(varchar(18), @total_errors) + ','
		+ convert(varchar(18), @total_errors - total_errors),
	Connections = convert(varchar(30),'Connections') + ','
                + convert(varchar(18), @connections) + ','
		+ convert(varchar(18), @connections - connections)
       from master..spt_monitor

/*
**  Now update master..spt_monitor
*/
update master..spt_monitor
	set
		lastrun = @now,
		cpu_busy = @cpu_busy,
		io_busy = @io_busy,
		idle = @idle,
		pack_received = @pack_received,
		pack_sent = @pack_sent,
		connections = @connections,
		pack_errors = @pack_errors,
		total_read = @total_read,
		total_write = @total_write,
		total_errors = @total_errors

commit transaction
set nocount off
return
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/


/*****************************************************************************/
/* This stored procedure gets all the users that have particular permissions */
/*****************************************************************************/

print ''
print 'Creating sp_MScmd_permissions'
print ''
go
create procedure sp_MScmd_permissions
@cre_dflt bit, @cre_proc bit, @cre_rule bit, @cre_table bit, @cre_db bit,
@cre_view bit, @dmp_db bit, @dmp_tran bit
as

set nocount on

create table #tmp_permiss
(name	 varchar(30),
 dflt	 bit,
 creproc bit,
 crerule bit,
 cretble bit,
 credb	 bit,
 creview bit,
 dmpdb	 bit,
 dmptran bit
)

insert into #tmp_permiss
select user_name(uid),0,0,0,0,0,0,0,0 from sysusers

update #tmp_permiss set dflt = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Create Default')

update #tmp_permiss set creproc = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Create Procedure')

update #tmp_permiss set crerule = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Create Rule')

update #tmp_permiss set cretble = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Create Table')

update #tmp_permiss set credb = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Create Database')

update #tmp_permiss set creview = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Create View')

update #tmp_permiss set dmpdb = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Dump Database')

update #tmp_permiss set dmptran = 1 from sysprotects where
name = user_name(uid) and
protecttype = (select number from master..spt_values where name = 'Grant')
and action = (select number from master..spt_values where name = 'Dump Transaction')

set nocount off

select name from #tmp_permiss
where dflt = @cre_dflt and creproc = @cre_proc and crerule = @cre_rule
and cretble = @cre_table and credb = @cre_db and creview = @cre_view
and dmpdb = @dmp_db and dmptran = @dmp_tran
go

/*****************************************************************************/
/* This stored procedure gets all the users that have particular permissions */
/*****************************************************************************/

print ''
print 'Creating sp_MSuser_cmd_permissions'
print ''
go
create procedure sp_MSuser_cmd_permissions @username varchar(30)
as

/*  TCD 8/2/91 See if the user exists. */

if not exists (select * from dbo.sysusers
			 where name = @username)
begin
	raiserror 70003 'User doesn''t exist. The ''sa'' or ''dbo'' must have dropped the user since you last displayed the listing.'
	return
end

set nocount on

create table #tmp_permiss
(dflt	 bit,
 creproc bit,
 crerule bit,
 cretble bit,
 credb	 bit,
 creview bit,
 dmpdb	 bit,
 dmptran bit
)

if user_id(@username) = 1	 /* dbo has all permissions */
  insert into #tmp_permiss
  values (1,1,1,1,1,1,1,1)
else
begin
  insert into #tmp_permiss
  values (0,0,0,0,0,0,0,0)

  update #tmp_permiss set dflt = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Create Default')

  update #tmp_permiss set creproc = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Create Procedure')

  update #tmp_permiss set crerule = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Create Rule')

  update #tmp_permiss set cretble = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Create Table')

  update #tmp_permiss set credb = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Create Database')

  update #tmp_permiss set creview = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Create View')

  update #tmp_permiss set dmpdb = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Dump Database')

  update #tmp_permiss set dmptran = 1 from sysprotects where
  user_name(uid) = @username and
  protecttype = (select number from master..spt_values where name = 'Grant')
  and action = (select number from master..spt_values where name = 'Dump Transaction')
end

set nocount off

select * from #tmp_permiss
go


/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/****************************************************************************/
/* This stored procedure will get the name, device, size, and location of
   each segment for a particular database. You must be in the db to exec    */
/****************************************************************************/

print ''
print 'Creating sp_MSsegment_list'
print ''
go
create procedure sp_MSsegment_list as

declare @segbit int

/* Create a temporary table where we can store all the necessary information */

set nocount on

create table #spsegtab1
(name varchar(30) null,
 segnumber smallint,
 segbit int null
)

create table #spsegtab2
(
 name varchar(30) null,
 device varchar(30) null,
)

/* Get all the segments in the system and their status */
/* Set the segments on @devname master.dbo.sysusages. */
/*    If segment 31, power(2, 31) will overflow
      since segmap is an int.  We'll grab the machine-dependent
      bit mask from master.dbo.spt_values to set the right bit. */

insert into #spsegtab1 (name, segnumber) select name, segment from syssegments
update #spsegtab1 set segbit = power(2, segnumber) where segnumber < 31
update #spsegtab1 set segbit = low from master.dbo.spt_values
		  where type = 'E' and number = 2
			and segnumber >= 31


/* Populate the #spsegtab2 with the devices for each segment */


insert into #spsegtab2
select distinct #spsegtab1.name, d.name
from master.dbo.sysusages u, master.dbo.sysdevices d, master.dbo.spt_values v, #spsegtab1
		where u.segmap & #spsegtab1.segbit = #spsegtab1.segbit
			and d.low <= u.size + u.vstart
			and d.high >= u.size + u.vstart - 1
			and u.dbid = db_id()
			and d.status & 2 = 2
			and v.number = 1
			and v.type = 'E'


set nocount off

select * from #spsegtab2
order by name
go
/****************************************************************************/
/* This stored procedure will return a list of the devices that contain
   a segment of a given name.						    */
/****************************************************************************/

print ''
print 'Creating sp_MSsegment_devices'
print ''
go
create procedure sp_MSsegment_devices @segname varchar(30)		/* segment name */
as
declare	@segbit	int		/* this is the bit version of the segment # */
declare	@segment	int	/* the segment number of the segment */
set nocount on
/*
**  Set the bit position for the segment.
*/
select @segment = segment
	from syssegments
		where name = @segname

/*
**  Now set the segments on @devname master.dbo.sysusages.
*/
if (@segment < 31)
	select @segbit = power(2, @segment)
else
	/*
	**  Since this is segment 31, power(2, 31) will overflow
	**  since segmap is an int.  We'll grab the machine-dependent
	**  bit mask from master.dbo.spt_values to set the right bit.
	*/
	select @segbit = low
		from master.dbo.spt_values
			where type = 'E'
				and number = 2

select distinct device = d.name
	from master.dbo.sysusages u, master.dbo.sysdevices d,
		master.dbo.spt_values v
		where u.segmap & @segbit = @segbit
			and d.low <= u.size + u.vstart
			and d.high >= u.size + u.vstart - 1
			and u.dbid = db_id()
			and d.status & 2 = 2
			and v.number = 1
			and v.type = 'E'
set nocount off
go

/****************************************************************************/
/* This stored procedure will get the properties of a particular segment in
   in the system							    */
/****************************************************************************/

print ''
print 'Creating sp_MSseg_properties'
print ''
go
create procedure sp_MSseg_properties @segname varchar(30) as

declare @segbit int, @segsize varchar(10), @segment int, @segdevs varchar(255)

/*  TCD 8/2/91 See if the segment exists. */

if not exists (select * from syssegments
			 where name = @segname)
begin
	raiserror 70005 'Segment doesn''t exist. The ''sa'' or ''dbo'' must have dropped the segment since you last displayed the listing.'
	return
end

set nocount on

/* Set the bit position for the segment */

select @segment = segment from syssegments where name = @segname
if (@segment < 31)
	select @segbit = power(2, @segment)
else

	/*  Since this is segment 31, power(2, 31) will overflow
	**  since segmap is an int.  We'll grab the machine-dependent
	**  bit mask from master.dbo.spt_values to set the right bit. */

	 select @segbit = low from master.dbo.spt_values where type = 'E' and number = 2

/* Get the total size of the segment */

select @segsize = convert(varchar(20), sum(round((u.size * convert(float, v.low)) / 1048576, 0))) + 'MB'
from master.dbo.sysusages u, master.dbo.sysdevices d, master.dbo.spt_values v
where u.segmap & @segbit = @segbit
      and d.low <= u.size + u.vstart
      and d.high >= u.size + u.vstart - 1
      and u.dbid = db_id()
      and d.status & 2 = 2
      and v.number = 1
      and v.type = 'E'

set nocount off

/* output the segment number and the segment size */

select segment_number = @segment, segment_size = @segsize

/* output the device names */

select distinct d.name
from master.dbo.sysusages u, master.dbo.sysdevices d, master.dbo.spt_values v
where u.segmap & @segbit = @segbit
      and d.low <= u.size + u.vstart
      and d.high >= u.size + u.vstart - 1
      and u.dbid = db_id()
      and d.status & 2 = 2
      and v.number = 1
      and v.type = 'E'

go

/****************************************************************************/
/* This stored procedure will get the tables/indexes for a particular
   segment in the system						    */
/****************************************************************************/

print ''
print 'Creating sp_MSseg_tables'
print ''
go
create procedure sp_MSseg_tables @segname varchar(30) as
select table_name = object_name(i.id), index_name = i.name, i.indid
from sysindexes i, syssegments s
where s.name = @segname and s.segment = i.segment
order by table_name, indid
go
/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*****************************************************************************/
/* This stored procedure gets all the options set for a particular database  */
/* It applies to the current database.					     */
/* It is generally called by sp_MSdb_properties 			     */
/*****************************************************************************/


print ''
print 'Creating sp_MSdb_options_bits'
print ''
go
create procedure sp_MSdb_options_bits as
declare @bitdesc varchar(255), @curdbid int, @allopts int
select @curdbid = db_id()
select @allopts = 7196

set nocount on

create table #spdbbits
(allopts bit,
 dbuse bit,
 chkpt bit,
 readonly bit,
 bulkload bit,
 single bit,
 trunc bit)

insert into #spdbbits values(0,0,0,0,0,0,0)

/*  Check all settable options (7196) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 7196 and v.number = @allopts	    /* all options */

if @bitdesc != null
begin
  update #spdbbits set allopts = 1
end

/* Check select into/bulk copy bit (4)	*/

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 4 and v.number != @allopts	  /* all options */

if @bitdesc != null
begin
   update #spdbbits set bulkload = 1
end

/*  Check no checkpoint on recovery bit (16)  */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 16 and v.number != @allopts /* all options */

if @bitdesc != null
begin
  update #spdbbits set chkpt = 1
end

/* Check single user bit (4096) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 4096 and v.number != @allopts	     /* all options */

if @bitdesc != null
begin
  update #spdbbits set single = 1
end

/*  Check dbo only bit (2048) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 2048 and v.number != @allopts	     /* all options */

if @bitdesc != null
begin
  update #spdbbits set dbuse = 1
end

/*  Check read only bit (1024) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 1024 and v.number != @allopts	     /* all options */

if @bitdesc != null
begin
  update #spdbbits set readonly = 1
end

/*  Check truncate log on checkpoint bit (8) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 8 and v.number != @allopts	  /* all options */

if @bitdesc != null
begin
  update #spdbbits set trunc = 1
end

set nocount off

select * from #spdbbits
go


/*****************************************************************************/
/* This procedure gets the devices that a database resides on for segment use */
/*****************************************************************************/

print ''
print 'Creating sp_MSdb_devices_segments'
print ''
go
create procedure sp_MSdb_devices_segments @dbname varchar(30) as

/*  TCD 8/2/91 See if the database exists. */

if not exists (select * from master.dbo.sysdatabases
			 where name = @dbname)
begin
	raiserror 70006 'Database doesn''t exist. The ''sa'' or ''dbo'' must have dropped the database since you last displayed the listing.'
	return
end

select distinct device = master.dbo.sysdevices.name
from master.dbo.sysdatabases, master.dbo.sysusages, master.dbo.sysdevices, master.dbo.spt_values a, master.dbo.spt_values b
where master.dbo.sysdatabases.dbid = master.dbo.sysusages.dbid
		and master.dbo.sysdevices.low <= size + vstart
		and master.dbo.sysdevices.high >= size + vstart - 1
		and master.dbo.sysdevices.status & 2 = 2
		and master.dbo.sysdatabases.name = @dbname
		and a.type = 'E' and a.number = 1 and b.type = 'S'
		and master.dbo.sysusages.segmap & 7 = b.number
		order by 1
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/


/*****************************************************************************/
/* This procedure gets the space used by a particular database. 	     */
/* You must be in the database to access this information		     */
/*****************************************************************************/


print ''
print 'Creating sp_MSdb_space'
print ''
go
create procedure sp_MSdb_space as
declare @options varchar(255)


declare @pages	float                  /* working variable for size calc. */
declare @dbsize float		       /* Size of the database */


set nocount on

create table #spt_space
(
 db_size	 float null,
 rows		 int null,
 reserved	 float null,
 data		 float null,
 indexp 	 float null,
 unused 	 float null,
 log_size	 float null,
 log_used	 float null
)

/* Get the total size of the database */

declare @pagesize int
select @pagesize = low from master..spt_values where number = 1 and type = 'E'
insert into #spt_space (db_size)
select distinct (sum(convert(float,size)) * (convert(float,@pagesize)) / 1048576.0)
from master.dbo.sysusages
where dbid = db_id()

/* */
/*  Now calculate the summary data.
    reserved: sum(reserved) where indid in (0, 1, 255) */
/* */
update #spt_space
set reserved = (select sum(convert(float,sysindexes.reserved)) from sysindexes where indid in (0, 1, 255))

/* */
/* data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text) */
/* */
select @pages = sum(convert(float,dpages)) from sysindexes
where indid < 2

select @pages = @pages + isnull(sum(convert(float,used)), 0.0) from sysindexes
where indid = 255

update #spt_space set data = @pages

	
/* index: sum(used) where indid in (0, 1, 255) - data */

update #spt_space
set indexp = ((select (sum(convert(float,used))) from sysindexes where indid in (0, 1, 255)) - data)

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved - (select sum(convert(float,used)) from sysindexes where indid in (0, 1, 255))

/* log_size: sum(reserved) where indid in (0, 1, 255) and object is 'syslogs' */

update #spt_space
set log_size = (select sum(convert(float,sysindexes.reserved)) from sysindexes where indid in (0, 1, 255) and id = object_id('syslogs'))

/* log_used: sum(used) where indid in (0, 1, 255) and id = object_id('syslogs') */

update #spt_space set log_used = (select sum(convert(float,used))
from sysindexes
where indid in (0, 1, 255) and id = object_id('syslogs'))

set nocount off

select reserved = substring((convert(varchar(11), (convert(int,(reserved * (convert(float,d.low) ) / 1024.0)))) + ' KB'),1, 15),
       data = substring((convert(varchar(11),convert(int,(data * convert(float,d.low)) / 1024.0)) + ' KB'), 1, 15),
       log_space = substring ((convert(varchar(11), (round((log_size * convert(float, d.low)) / 1024.0, 0))) + ' KB'), 1, 15),
       index_size = substring((convert(varchar(11), convert(int,(indexp * convert(float,d.low)) / 1024.0)) + ' KB'), 1, 15),
       unused_reserve = substring((convert(varchar(11),convert(int,(unused * convert(float,d.low)) / 1024.0)) + ' KB'), 1, 15),
       unused_log = substring ((convert(varchar(11), (round(((log_size - log_used) * convert(float, d.low)) / 1024.0, 0))) + ' KB'), 1, 15)
       from #spt_space, master.dbo.spt_values d
       where d.number = 1 and d.type = 'E'
go


/*****************************************************************************/
/* This stored procedure gets all the options set for a particular database  */
/* It applies to the current database.					     */
/* It is generally called by sp_MSdb_properties 			     */
/*****************************************************************************/


print ''
print 'Creating sp_MSdb_options'
print ''
go
create procedure sp_MSdb_options as
declare @options varchar(255), @bitdesc varchar(255), @curdbid int, @allopts int
select @curdbid = db_id()
select @allopts = 7196

set nocount on

select @options = ''

/* Check select into/bulk copy bit (4)	*/

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 4 and v.number != @allopts	  /* all options */

if @bitdesc != null
begin
   if @options != ''
     select @options = @options + ', ' +  @bitdesc
   else select @options = @bitdesc
end

/*  Check no checkpoint on recovery bit (16)  */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 16 and v.number != @allopts /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/* Check single user bit (4096) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 4096 and v.number != @allopts	     /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/*  Check dbo only bit (2048) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 2048 and v.number != @allopts	     /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/*  Check read only bit (1024) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 1024 and v.number != @allopts	     /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/*  Check load only bit (512) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 512 and v.number != @allopts	    /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/* Check not recovered only bit (256)  */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 256 and v.number != @allopts  /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/* Check don't recover bit (32)  */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 32 and v.number != @allopts	   /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/*  Check truncate log on checkpoint bit (8) */

select @bitdesc = null
select @bitdesc = v.name from master.dbo.spt_values v, master.dbo.sysdatabases d
       where d.dbid = @curdbid and v.type = 'D'
       and d.status & v.number = 8 and v.number != @allopts	  /* all options */

if @bitdesc != null
begin
  if @options != ''
     select @options = @options + ', ' +  @bitdesc
  else select @options = @bitdesc
end

/* If no flags are set, say so. */

if @options = ''
begin
  select @options = 'no options set'
end

set nocount off

select @options
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*****************************************************************************/
/* This procedure gets the devices that a database resides on.		     */
/*****************************************************************************/

print ''
print 'Creating sp_MSdb_devices'
print ''
go
create procedure sp_MSdb_devices @dbname varchar(30) as

select device = master.dbo.sysdevices.name,
       size = convert(varchar(10), round((a.low * convert(float, size))/ 1048576, 0)) + ' ' + 'MB',
       usage = b.name
from master.dbo.sysdatabases, master.dbo.sysusages, master.dbo.sysdevices, master.dbo.spt_values a, master.dbo.spt_values b
where master.dbo.sysdatabases.dbid = master.dbo.sysusages.dbid
      and master.dbo.sysdevices.low <= size + vstart
      and master.dbo.sysdevices.high >= size + vstart - 1
      and master.dbo.sysdevices.status & 2 = 2
      and master.dbo.sysdatabases.name = @dbname
      and a.type = 'E' and a.number = 1 and b.type = 'S'
      and master.dbo.sysusages.segmap & 7 = b.number
      order by 1
go

/*****************************************************************************/
/* These routines will get all the necessary data for the Database Properties */
/* dialog box.	You must be in the database to access this information	     */
/*****************************************************************************/

print ''
print 'Creating sp_MSdb_properties'
print ''
go
create procedure sp_MSdb_properties as
declare @dbname varchar(255), @users int, @groups int

/* Get number of users and number of groups */

set nocount on
select @users = count(*) from sysusers where uid < 16383 and uid > 0
select @groups = count(*) from sysusers where uid >= 16383 or uid = 0
set nocount off

select Number_users = @users, Number_groups = @groups

/* Get the object counts and db options */

exec sp_MSdb_options

/* Get the space used by the database */

exec sp_MSdb_space
go

/*****************************************************************/
/* This stored procedure will calculate thread usage             */
/****************************************************************/


print ''
print 'Creating sp_MSthread_list'
print ''
go
create procedure sp_MSthread_list as

set nocount on

declare @devs int, @servs int, @total int, @avail_threads int,
@avail_servs int, @mirrors int, @outline varchar(50)

select @devs = count(*) from master.dbo.sysdevices where cntrltype = 0
select @mirrors = count(*) from master.dbo.sysdevices where status & 512 = 512
select @servs = 2 * count(*) from master.dbo.sysservers where srvid != 0
select @total = 4 + @devs + @mirrors+ @servs
select @avail_threads = 53-4-@devs-@mirrors-@servs
if @avail_threads % 2 = 1
    select @avail_servs = (@avail_threads - 1)/2
else
    select @avail_servs = @avail_threads/2


set nocount off
select '4 SQL Server Threads'
select @outline = convert(varchar(5), @devs+@mirrors)+ ' Database Device(s) (including mirrors)'
select @outline
select @outline = convert(varchar(5), @servs/2)+ ' Remote Server(s)'
select @outline
select @outline = convert(varchar(5),  @total) + ' Threads Currently Used'
select @outline
select @outline = 'You May Create Up To ' + convert(varchar(5), (53 - @total)) + ' More Devices'
select @outline
select @outline = 'Or You May Create Up To ' + convert(varchar(5), @avail_servs) + ' Remote Servers'
select @outline
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/***************************************************************************/
/* This stored procedure is a copy of sp_helpdb, but returns the full      */
/* length of the database information columns.  In cases where the db name */
/* is at or near 30 characters, sp_helpdb would truncate the name.         */
/***************************************************************************/
print ''
print 'Creating sp_MShelpdb'
print ''
go

create procedure sp_MShelpdb
@dbname varchar(30) = NULL			/* database name to change */
as

declare @showdev	bit
declare @allopts	int

set nocount on

/*
**  If no database name given, get 'em all.
*/
if @dbname = null
	select @dbname = '%', @showdev = 0
else select @showdev = 1

/*
**  Sure the database exists
*/
if not exists (select *
	from master.dbo.sysdatabases
		where name like @dbname)
begin
	print 'No such database exists.'
	return (1)
end

/*
**  Set allopts to be the sum of all possible user-settable database status
**  bits.
*/
select @allopts = 7196

/*
**  Since we examine the status bits in sysdatabase and turn them
**  into english, we need a temporary table to build the descriptions.
*/
create table #spdbdesc
(
	dbid	smallint null,
	dbdesc	varchar(102) null
)

/*
**  Initialize #spdbdesc from master.dbo.sysdatabases
*/
insert into #spdbdesc (dbid)
		select dbid
			from master.dbo.sysdatabases
				where name like @dbname

/*
**  Now for each dbid in #spdbdesc, build the database status
**  description.
*/
declare @curdbid smallint		/* the one we're currently working on */
declare @dbdesc varchar(102)		/* the total description for the db */
declare @bitdesc varchar(30)		/* the bit description for the db */

/*
**  Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid)
	from #spdbdesc
while @curdbid != NULL
begin
	/*
	**  Initialize @dbdesc.
	*/
	select @dbdesc = ''

	/*
	**  Check select into/bulk copy bit (4)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 4
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check no checkpoint on recovery bit (16)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 16
				and v.number != @allopts /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check single user bit (4096)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 4096
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check dbo only bit (2048)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 2048
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check read only bit (1024)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 1024
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check load only bit (512)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 512
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check not recovered only bit (256)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 256
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check don't recover bit (32)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 32
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  Check truncate log on checkpoint bit (8)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.dbid = @curdbid
				and v.type = 'D'
				and d.status & v.number = 8
				and v.number != @allopts       /* all options */
	if @bitdesc != null
	begin
		if @dbdesc != ''
			select @dbdesc = @dbdesc + ', ' +  @bitdesc
		else select @dbdesc = @bitdesc
	end

	/*
	**  If not flags are set, say so.
	*/
	if @dbdesc = ''
	begin
		select @dbdesc = 'no options set'
	end

	/*
	**  Save the description.
	*/
	update #spdbdesc
		set dbdesc = @dbdesc
			from #spdbdesc
				where dbid = @curdbid

	/*
	**  Now get the next, if any dbid.
	*/
	select @curdbid = min(dbid)
		from #spdbdesc
			where dbid > @curdbid
end

/*
**  Now #spdbdesc is complete so we can print out the db info
*/

select distinct name = d.name,
		db_size = substring( convert( varchar(10), round( convert(
                  float,v.low * (select sum(convert(float,u.size)) from master.dbo.sysusages u where dbid = d.dbid)
      ) / 1048576.0, 0 ) ) + 'MB', 1, 8 ),
		owner = suser_name(suid),
      dbid = d.dbid,
		created = convert(char(11), d.crdate),
		status = #spdbdesc.dbdesc
from master.dbo.sysdatabases d, master.dbo.spt_values v, #spdbdesc
      where v.type = 'E' and v.number = 1
      and d.dbid = #spdbdesc.dbid
order by d.name

/*
**  If we are looking at one database, show it's device allocation.
*/
if @showdev = 1
begin
	select device_fragments = master.dbo.sysdevices.name, size =
		convert(varchar(10),
			round(
			(a.low * convert(float, size))
			/ 1048576, 0)) + ' ' + 'MB',
		usage = b.name
	from master.dbo.sysdatabases, master.dbo.sysusages, master.dbo.sysdevices, master.dbo.spt_values a, master.dbo.spt_values b
		where master.dbo.sysdatabases.dbid = master.dbo.sysusages.dbid
			and master.dbo.sysdevices.low <= size + vstart
			and master.dbo.sysdevices.high >= size + vstart - 1
			and master.dbo.sysdevices.status & 2 = 2
			and master.dbo.sysdatabases.name = @dbname
			and a.type = 'E'
			and a.number = 1
			and b.type = 'S'
			and master.dbo.sysusages.segmap & 7 = b.number
	order by 1

	/*
	**  If there is only one database and we are in it, show the
	**  segments.
	*/
	if exists (select *
			from #spdbdesc
				where db_id() = dbid)
	begin
		declare @curdevice	varchar(30),
			@curseg		smallint,
			@segbit		int

		delete #spdbdesc

		select @curdevice = min(d.name)
			from  master.dbo.sysusages u, master.dbo.sysdevices d
				where u.dbid = db_id()
					and d.low <= size + vstart
					and d.high >= size + vstart - 1
					and d.status & 2 = 2
		while (@curdevice != null)
		begin
			/*
			** We need an inner loop here to go through
			**  all the possible segment.
			*/
			select @curseg = min(segment)
					from syssegments
			while (@curseg != null)
			begin
				if (@curseg < 31)
					select @segbit = power(2, @curseg)
				else select @segbit = low
					from master.dbo.spt_values
						where type = 'E'
							and number = 2
				insert into #spdbdesc
					select @curseg, @curdevice
						from master.dbo.sysusages u,
							master.dbo.sysdevices d,
							master.dbo.spt_values v
					where u.segmap & @segbit = @segbit
						and d.low <= u.size + u.vstart
						and d.high >= u.size + u.vstart - 1
						and u.dbid = db_id()
						and d.status & 2 = 2
						and v.number = 1
						and v.type = 'E'
						and d.name = @curdevice
				select @curseg = min(segment)
						from syssegments
							where segment > @curseg
			end

			select @curdevice = min(d.name)
				from  master.dbo.sysusages u,
					master.dbo.sysdevices d
				where u.dbid = db_id()
					and d.low <= size + vstart
					and d.high >= size + vstart - 1
					and d.status & 2 = 2
					and d.name > @curdevice
		end

		/*
		**  One last check for any devices that have no segments.
		*/
		insert into #spdbdesc
			select null, d.name
				from master.dbo.sysusages u,
					master.dbo.sysdevices d
			where u.segmap = 0
				and d.low <= u.size + u.vstart
				and d.high >= u.size + u.vstart - 1
				and u.dbid = db_id()
				and d.status & 2 = 2

		select distinct device = dbdesc,
			segment = isnull(name, ' -- unused by any segments --')
		from #spdbdesc, syssegments
			where dbid *= segment
		order by 1, 2
	end

end

drop table #spdbdesc
return (0)
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/***************************************************************************/
/* This stored procedure is a copy of sp_helpremotelogin, but returns the  */
/* full length of the login information columns.  In cases where the       */
/* remote login name is at or near 30 characters, sp_helpremotelogins      */
/* would truncate the name.                                                */
/***************************************************************************/
print ''
print 'Creating sp_MShelpremotelogin'
print ''
go

create procedure sp_MShelpremotelogin
@remoteserver varchar(30) = NULL,	/* remote server name */
@remotename varchar(30) = NULL		/* remote login name */
as

declare	@msg	varchar(100)

set nocount on

/*
**  If no server given, get 'em all.
*/
if @remoteserver = null
begin
	select @remoteserver = '%'
end

else
begin
	if not exists (select *
			from master.dbo.sysservers s,
				master.dbo.sysremotelogins r
			where s.srvid = r.remoteserverid
				and s.srvname like @remoteserver)
	begin
		if @remoteserver = '%'
		begin
			select @msg = 'There are no remote servers defined.'
			print @msg
			return (1)
		end
		return (1)
	end
end

/*
**  If no remotename given, get 'em all.
*/
if @remotename = null
begin
	select @remotename = '%'
end

else
begin
	if not exists (select *
			from master.dbo.sysremotelogins
			where isnull(remoteusername, '') like @remotename)
	begin
		if @remotename = '%'
		begin
			select @msg = 'There are no remotelogins defined.'
			print @msg
			return (1)
		end

		select @msg = 'There are no remote logins for '''
			+ @remotename + '''.'
		print @msg
		return (1)
	end
end

/*
**  Check for empty results.
*/
if not exists (select *
		from master.dbo.sysremotelogins r, master.dbo.sysservers s
		where isnull(r.remoteusername, '') like @remotename
			and s.srvid = r.remoteserverid
				and s.srvname like @remoteserver)
begin
	if ((@remoteserver = '%') and (@remotename = '%'))
		select @msg = 'There are no remote logins.'
	else select @msg = 'There are no remote logins for '''
		+ @remotename + ''' on remote server ''' + @remoteserver + '''.'
	print @msg
	return (1)
end

/*
**  Select the information.
*/
select server = s.srvname,
	local_user_name =
		isnull(suser_name(r.suid), '** use local name **'),
	remote_user_name =
		isnull(r.remoteusername, '** mapped locally **'),
	options = v.name
		from master.dbo.sysservers s, master.dbo.sysremotelogins r,
			master.dbo.spt_values v
	where s.srvid = r.remoteserverid
		and s.srvname like @remoteserver
		and isnull(r.remoteusername, '') like @remotename
		and v.type = 'F'
		and v.number = r.status
order by server, remote_user_name

return (0)
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/***************************************************************************/
/* This stored procedure is a copy of sp_helpserver, but returns the       */
/* full length of the server information columns.  In cases where the      */
/* server name is at or near 30 characters, sp_helpserver would truncate   */
/* the name.                                                               */
/***************************************************************************/
print ''
print 'Creating sp_MShelpserver'
print ''
go

create procedure sp_MShelpserver
@server varchar(30) = NULL		/* server name */
as

declare	@msg	varchar(100)

set nocount on

/*
**  If no server name given, get 'em all.
*/
if @server = null
begin
	select @server = '%'
end

/*
**  Does the server exist?
*/
if not exists (select *
		from master.dbo.sysservers
			where srvname like @server
				or srvnetname like @server)
begin
	if @server = '%'
		select @msg = 'There are no servers defined.'
	else select @msg = 'There is no server named ''' + @server + '''.'
	print @msg
	return (1)
end

/*
**  Display server information.
**  First put it into a temp table so we can blot inappropriate status
**  for the local server.  It's ugly but I couldn't think of another way.
*/
create table #spt_server
(
	name	varchar(30),
	network	varchar(30),
	status	varchar(20),
	id	smallint
)
insert into #spt_server
	select s.srvname, s.srvnetname, status = isnull(v.name, ''),
		id = s.srvid
	from master.dbo.sysservers s, master.dbo.spt_values v
		where s.srvstatus *= v.number
			and v.type = 'A'
			and (s.srvname like @server
				or s.srvnetname like @server)
update #spt_server
	set status = ''
		from master.dbo.sysservers s, #spt_server t
			where s.srvid = 0
				and s.srvname = t.name

/* Sql95 uses totally different status bits and timeouts has lost its */
/* meaning, so always clear this if we're on sql95 */
update #spt_server set status = '' where @@microsoftversion & 0x00FFFFFF >= 0x00050000

select name, network_name = network,
	status = status, id = convert(char(4), id)
		from #spt_server
	order by name

return (0)
go


/***************************************************************************/
/* This section grants execute permission on the stored procedure and
   select permissions on all the tables 				   */
/***************************************************************************/

/* */
/* Grant permission on the procedures */
/* */

print ''
print 'Granting execute permissions on procedures'
print ''
go
grant execute on sp_MSdevice_list to public
grant execute on sp_MScheck_admin to public
grant execute on sp_MSdev_description to public
grant execute on sp_MSdev_databases to public
grant execute on sp_MSsys_monitor to public
grant execute on sp_MSnext_devnumber to public
grant execute on sp_MSdatabase_list to public
grant execute on sp_MSdatabase_avail to public
grant execute on sp_MSbackup_now to public
grant execute on sp_MSuser_list to public
grant execute on sp_MSmonitor to public
grant execute on sp_MSdb_devices to public
grant execute on sp_MSdb_options to public
grant execute on sp_MSdb_properties to public
grant execute on sp_MSdb_space to public
grant execute on sp_MSbackup_schedule to public
grant execute on sp_MScmd_permissions to public
grant execute on sp_MSuser_cmd_permissions to public
grant execute on sp_MSdb_options_bits to public

/* */
/* these are 4.2 only */
/* */

grant execute on sp_MSdev_mirror to public
grant execute on sp_MSdb_devices_segments to public
grant execute on sp_MSsegment_list to public
grant execute on sp_MSsegment_devices to public
grant execute on sp_MSseg_properties to public
grant execute on sp_MSseg_tables to public
grant execute on sp_MSthread_list to public
grant execute on sp_MShelpdb to public
grant execute on sp_MShelpremotelogin to public
grant execute on sp_MShelpserver to public
go

/* */
/* Grant permissions on tables */
/* */

print ''
print 'Creating permissions on tables'
print ''
go
grant select, update, delete, insert on MSscheduled_backups to dbo
grant select, update, delete, insert on MSscheduled_backups_log to dbo
go


/* logdevice    27.1    3/5/90 */
/* rv, 2/4/92, bug 1265 */

sp_configure 'allow updates',1
go
reconfigure with override
go

create procedure sp_MSlogdevice
@dbname varchar(30),            /* database name that has the syslogs */
@devname        varchar(30),    /* device name to put syslogs on */
@fragflag       varchar(3)=' '  /* if ='*', change all fragments */
as

declare @dbid   smallint        /* dbid of the database to be changed */
declare @dbuid  smallint        /* id of the owner of the database */
declare @logbit int             /* this is the bit to turn on in sysusages */
declare @fragcnt smallint       /* count of allocation fragments */
declare @fragstart int          /* lowest lstart of fragments to change */

select @logbit = 4              /* bit 3 is the one to turn on */
select @fragstart = 0           /* lowest lstart of fragments to change */

/*
**  Verify the database name and get the @dbid and @dbuid
*/
select @dbid = dbid, @dbuid = suid
        from sysdatabases
                where name = @dbname

/*
**  If @dbname not found, say so and list the databases.
*/
if @dbid = NULL
begin
        print 'No such database -- run sp_helpdb to list databases.'
        return (1)
end

/*
**  See if the device exists.
*/
if not exists (select *
                from master.dbo.sysdevices
                        where name like @devname)
begin
        print 'No such device exists -- run sp_helpdevice to list the SQLServer devices.'
        return (1)
end

/*
**  You must be SA or the dbo to execute this sproc.
*/
if suser_id() != 1 and suser_id() != @dbuid
begin
        print 'Only the System Administrator (SA) or the Database Owner (dbo) may move the syslogs table.'
        return (1)
end

/*
**  Now see if the @dbname uses the @devname
*/
if not exists (select *
                from sysusages u, sysdevices d
                        where d.name = @devname
                                and u.vstart between d.low and d.high
                                and u.dbid = @dbid)
begin
        print 'The specified device is not used by the database.'
        return (1)
end

/*
**  Count the allocation fragments on the specified device, excluding
**  the one containing the system tables (lstart=0).
*/
select @fragcnt=count(*)
        from master.dbo.sysusages u, master.dbo.sysdevices d
                where d.name = @devname
                        and u.vstart between d.low and d.high
                        and u.dbid = @dbid
                        and lstart != 0
/*
**  If there are none, it cannot be changed to log-only status
*/
if @fragcnt = 0
begin
     print 'Can''t make the only segment on original device log-only'
     return (1)
end

/*
**  If more than one allocation fragment and '*' option not specified,
**  change only the one with the highest lstart.
*/
if @fragcnt > 1 and @fragflag!='*'
begin
     print 'Warning, there are multiple allocation fragments on this device.'
     print 'Only the last fragment will be changed to log-only status.  If you'
     print 'wish to change them all, run sp_MSlogdevice database,device,''*'' '

     select @fragstart=max(lstart)
        from master.dbo.sysusages u, master.dbo.sysdevices d
                where d.name = @devname
                        and u.vstart between d.low and d.high
                        and u.dbid = @dbid
end


/*
**  clear the bit from any database segments that aren't already log only
*/
update master.dbo.sysusages
        set segmap = segmap & ~@logbit
                from master.dbo.sysusages
                        where dbid = @dbid
                        and segmap != @logbit
/*
**  Now set the segments on @devname as log-only.
*/
update master.dbo.sysusages
        set segmap = @logbit
                from master.dbo.sysusages u, master.dbo.sysdevices d
                        where d.name = @devname
                                and u.vstart between d.low and d.high
                                and u.dbid = @dbid
                                and lstart != 0
                                and lstart>=@fragstart

/*
**  Now we need to activate the new segment map.
*/
dbcc dbrepair (@dbname, remap)
print 'Syslogs moved.'

return (0)
go
sp_configure 'allow updates',0
go
print ''
print 'reconfiguring'
print ''
go

reconfigure with override
go


grant execute on sp_MSlogdevice to public
go


/************* SQL ADMIN VERSION CONTROL *************************************/
/*    4.20.xx.yy where yy is script version.                                 */
/*****************************************************************************/
create procedure sp_MSAdmin_version as
   select 'Microsoft SQL Administrator script version 6.00.00.1'
go

print ''
print 'Reconfiguring to set the category bit'
print ''
go
sp_configure 'allow updates',1
go
reconfigure with override
go

/* Set the 'MS-installed object' category bit.  Must be done AFTER all objects created. */
update master..sysobjects set category = category | 0x02 where sysstat & 7 = 4 and name in (
      'sp_MSdevice_list',
      'sp_MScheck_admin',
      'sp_MSdev_description',
      'sp_MSdev_databases',
      'sp_MSsys_monitor',
      'sp_MSnext_devnumber',
      'sp_MSdatabase_list',
      'sp_MSdatabase_avail',
      'sp_MSbackup_now',
      'sp_MSuser_list',
      'sp_MSmonitor',
      'sp_MSdb_devices',
      'sp_MSdb_options',
      'sp_MSdb_properties',
      'sp_MSdb_space',
      'sp_MSbackup_schedule',
      'sp_MScmd_permissions',
      'sp_MSuser_cmd_permissions',
      'sp_MSdb_options_bits',
      'sp_MSdev_mirror',
      'sp_MSdb_devices_segments',
      'sp_MSsegment_list',
      'sp_MSsegment_devices',
      'sp_MSseg_properties',
      'sp_MSseg_tables',
      'sp_MSthread_list',
      'sp_MShelpdb',
      'sp_MShelpremotelogin',
      'sp_MSlogdevice',
      'sp_MSAdmin_version',
      'sp_MShelpserver')
go
update master..sysobjects set category = category | 0x02 where sysstat & 7 = 3 and name in (
      'MSscheduled_backups',
      'MSscheduled_backups_log',
      'MSsystem_monitor',
      'MSlast_monitor')
go

sp_configure 'allow updates',0
go
reconfigure with override
go

set nocount on

create table #spmissing
(name varchar(30),
 type varchar(10)
)
go

/* */
/* Check for the stored procedures first */
/* */

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdevice_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdevice_list', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdev_description' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdev_description', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdev_databases' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdev_databases', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSsys_monitor' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSsys_monitor', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSnext_devnumber' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSnext_devnumber', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdatabase_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdatabase_list', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdatabase_avail' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdatabase_avail', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSbackup_now' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSbackup_now', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSuser_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSuser_list', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSmonitor' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSmonitor', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_devices' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_devices', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_options' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_options', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_properties' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_properties', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_space' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_space', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSbackup_schedule' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSbackup_schedule', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MScmd_permissions' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MScmd_permissions', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSuser_cmd_permissions' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MScmd_permissions', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_options_bits' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_options_bits', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdev_mirror' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdev_mirror', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdb_devices_segments' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSdb_devices_segments', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSsegment_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSsegment_list', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSsegment_devices' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSsegment_devices', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSseg_properties' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSseg_properties', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSseg_tables' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSseg_tables', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSthread_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSthread_list', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MShelpdb' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MShelpdb', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MShelpremotelogin' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSremotelogin', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MShelpserver' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MShelpserver', 'procedure')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSlogdevice' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSlogdevice', 'procedure')
end
go

/* */
/* Check for the tables next */
/* */

if not exists (select * from master.dbo.sysobjects where name = 'MSscheduled_backups')
begin
  insert into #spmissing values('MSschedule_backups','table')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'MSscheduled_backups_log')
begin
  insert into #spmissing values('MSschedule_backups_log','table')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'MSsystem_monitor')
begin
  insert into #spmissing values('MSsystem_monitor','table')
end
go
if not exists (select * from master.dbo.sysobjects where name = 'MSlast_monitor')
begin
  insert into #spmissing values('MSlast_monitor','table')
end
go

if exists (select * from #spmissing)
begin
   print ''
   print ''
   print ' ====================  ERRORS!  ===================='
   print '       The following objects were not created.'
   print ' Sql Administrator will not run against this server.'
   print ''
   select * from #spmissing
   drop procedure sp_MSAdmin_version
end
else
begin
   print ''
   print ''
   print ' Successful installation.'
   grant execute on sp_MSAdmin_version to public
   exec sp_MSAdmin_version
end
go
drop table #spmissing
go
set nocount off
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/
checkpoint
go
