Liren Large Software Subsidy 5/05.iso/a/a061/8.img/ADMIN2.SQ@/ADMIN2.bin

Text File | 1992-02-26 | 89.6 KB | 2,643 lines

/*******************************************************************************/
/*  SQL Administrator INSTALL SCRIPT FOR SQL SERVER VERSION 4.2            */
/*******************************************************************************/

/************* 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 = "MSscheduled_backups")
begin
  drop table MSscheduled_backups
end
if exists (select * from sysobjects where name = "MSscheduled_backups_log")
begin
    drop table MSscheduled_backups_log
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_MSlogdevice' and sysstat & 7 = 4)
begin
  drop procedure sp_MSlogdevice
end
go

/***************************************************************************/
/* This stored procedure checks to make sure all the stored procedures and
   tables that the SQL Server Admin tool uses are actually on the server   */
/***************************************************************************/

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

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        */
/****************************************************************************/

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 int null,
 freespaceout varchar(20) null,
 mirror varchar(10) null
)

declare @alloc int
select @alloc = 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)
select d.name, ((d.high - d.low) * @alloc + 500000) / 1048576,
ltrim(str((((d.high - d.low) * @alloc + 500000)/1048576), 8,0)) + " MB", sum((usg.size * @alloc + 500000) / 1048576), "none"
from sysdevices d, sysusages usg
where d.low <= usg.size + usg.vstart - 1
and d.high >= usg.size + usg.vstart - 1
and d.cntrltype = 0
group by d.name

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

insert into #spdevtab (name, devsize, devsizeout, spaceused, mirror)
select d.name, ((d.high - d.low) * @alloc + 500000) / 1048576,
ltrim(str((((d.high - d.low) * @alloc + 500000)/1048576), 8,0)) + " MB", 0, "none"
from sysdevices d, sysusages usg
where d.cntrltype = 0 and
d.name not in (select name from #spdevtab)
group by d.name

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

insert #spdevtab (name, devsizeout, freespaceout, mirror)
select d.name, "*******","*****", "*******"
from sysdevices d, #spdevtab s where
d.name not in (select name from #spdevtab)

/* Calculate the free space each disk device */

update #spdevtab set #spdevtab.freespaceout = convert(varchar(10),#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 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 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, master.dbo.spt_values v, #spdevtab
        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, master.dbo.spt_values v, #spdevtab
        where d.status & 512 != 512 and d.status >= 32 and #spdevtab.name = d.name
   end
end

/* */
/* 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 = d.low / 16777216, 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 tinyint, @maxdev tinyint
select @nextdev = 1
select @maxdev= value from master..sysconfigures where config=116

while @nextdev != @maxdev /* This is one max device number allowed */
begin
  if exists(select name from sysdevices where (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 int
select @low = 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(usg.size) * @low) / 1048576,1))) + " 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                                         */
/************************************************************************/

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


print ""
print "Creating MSscheduled_backups"
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

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

print ""
print "Creating MSscheduled_backups_log"
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,    /* 0 = SUCCESS 1 = 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 spt_monitor.cpu_busy.
**  If it has update 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

create table #spdbdesc        /* Create temporary table for storage. */
(
 dbid     smallint null
)

set nocount on

insert into #spdbdesc (dbid)
       select dbid from sysdatabases

set nocount off

select distinct name = sysdatabases.name,
        db_size = substring(convert(varchar(10),
            (spt_values.low * sum(sysusages.size)) / 1048576)
            + "MB", 1, 8),
        owner = syslogins.name,
                spaceavail = "         ",
        created = convert(char(11), sysdatabases.crdate)
from sysdatabases, syslogins, sysusages, spt_values, #spdbdesc
where sysdatabases.dbid = #spdbdesc.dbid
      and sysdatabases.suid = syslogins.suid
      and #spdbdesc.dbid = sysusages.dbid
      and spt_values.type = "E"
      and spt_values.number = 1
group by #spdbdesc.dbid
having sysdatabases.dbid = #spdbdesc.dbid
       and sysdatabases.suid = syslogins.suid
       and #spdbdesc.dbid = sysusages.dbid
       and spt_values.type = "E"
       and spt_values.number = 1
order by sysdatabases.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 int, @reserved int, @unused int, @low int

set nocount on

select @db_size =  ((sum(size) * d.low) / 1048576) * 1024
           from master.dbo.sysusages, master.dbo.spt_values d
           where dbid = db_id() and d.number = 1 and d.type = "E"
           having dbid = db_id() and d.number = 1 and d.type = "E"

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

select @low = d.low from master.dbo.spt_values d where d.number = 1 and d.type = "E"

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

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

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

set nocount off

select space_avail = convert(varchar(10),@db_size - (@reserved - @unused)) + "KB"
go

/****************************************************************************/
/* This stored procedure will get all the databases that need to be dumped
   at the time of inquiry */
/****************************************************************************/


print ""
print "Creating sp_MSbackup_now"
print ""
go
create procedure sp_MSbackup_now as
declare @now datetime, @dayofweek tinyint, @hour tinyint, @minute tinyint,
@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()
select @dayofweek = datepart(weekday,@now)
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 (Day = @dayofweek or Frequency = 1)             /* Dump today or Daily */
      and Frequency <= 14                     /* Freq daily, weekly, or biweekly */
      and datediff(day, Last_dump, @now) >= Frequency         /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= Frequency*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= 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 tinyint     /* 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 (Day = @dayofweek)                     /* Dump today */
      and Frequency >= 31                     /* Freq monthly */
      and Frequency - 30 = @weekno                 /* Week of month */
      and datediff(day, Last_dump, @now) >= 30             /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= Frequency*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= Frequency*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") + ","
                + 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") + ","
                + 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") + ","
                + 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,
 size varchar(10) null,
)

/* Get all the segments in the system and their status */
/* Set the segments on @devname sysusages. */
/*    If segment 31, power(2, 31) will overflow
      since segmap is an int.  We'll grab the machine-dependent
      bit mask from 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, convert(varchar(20), round((u.size * convert(float, v.low)) / 1048576, 0)) + "MB"
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 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 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 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 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 = sysdevices.name
from sysdatabases, sysusages, sysdevices, spt_values a, spt_values b
where sysdatabases.dbid = sysusages.dbid
        and sysdevices.low <= size + vstart
        and sysdevices.high >= size + vstart - 1
        and sysdevices.status & 2 = 2
        and sysdatabases.name = @dbname
        and a.type = "E" and a.number = 1 and b.type = "S"
        and 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    int               /* working variable for size calc. */
declare @dbsize int               /* Size of the database */

/* Create a temp table to do the calculation. */
/*  reserved: sum(reserved) where indid in (0, 1, 255)
    data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
    index: sum(used) where indid in (0, 1, 255) - data
    unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/

set nocount on

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

/* Get the total size of the database */

insert into #spt_space (db_size)
select distinct (sum(size) * d.low) / 1048576
from master.dbo.sysusages, master.dbo.spt_values d
where dbid = db_id() and d.number = 1 and d.type = "E"
having dbid = db_id() and d.number = 1 and d.type = "E"

/* */
/*  Now calculate the summary data.
    reserved: sum(reserved) where indid in (0, 1, 255) */
/* */

update #spt_space
set reserved = (select sum(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(dpages) from sysindexes
where indid < 2

select @pages = @pages + isnull(sum(used), 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(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(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(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(used)
from sysindexes
where indid in (0, 1, 255) and id = object_id('syslogs'))

set nocount off

select reserved = substring(convert(varchar(11), (reserved * d.low ) / 1024) + " KB", 1, 15),
       data = substring(convert(varchar(11),(data * d.low) / 1024) + " KB", 1, 15),
       log_space = substring (convert(varchar(11), (round((log_size * convert(float, d.low)) / 1024, 0))) + " KB", 1, 15),
       index_size = substring(convert(varchar(11), (indexp * d.low) / 1024) + " KB", 1, 15),
       unused_reserve = substring(convert(varchar(11),(unused * d.low) / 1024) + " KB", 1, 15),
       unused_log = substring (convert(varchar(11), (round(((log_size - log_used) * convert(float, d.low)) / 1024, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 spt_values v, 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 = sysdevices.name,
       size = convert(varchar(10), round((spt_values.low * convert(float, size))/ 1048576, 0)) + " " + "MB",
       usage = b.name
from sysdatabases, sysusages, sysdevices, spt_values a, spt_values b
where sysdatabases.dbid = sysusages.dbid
      and sysdevices.low <= size + vstart
      and sysdevices.high >= size + vstart - 1
      and sysdevices.status & 2 = 2
      and sysdatabases.name = @dbname
      and a.type = "E" and a.number = 1 and b.type = "S"
      and 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 sysdevices where cntrltype = 0
select @mirrors = count(*) from sysdevices where status & 512 = 512
select @servs = 2 * count(*) from 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


/***************************************************************************/
/* 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_MScheck_admin to public
grant execute on sp_MSdevice_list 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
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
reconfigure with override
go


grant execute on sp_MSlogdevice to public
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