Piper's Pit BBS/FTP: ibm 0430 - 0439/ibm0430-0439/ibm0438.tar/ibm0438/SQL42109.ZIP/SQL421A.009/SQL/I386/ADMIN2.SQ@/ADMIN2.SQ@

Text File | 1994-07-14 | 123.5 KB | 3,655 lines

/***************************************************************************/
/*   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",
round(sum((convert(float,usg.size) * @alloc) / 1048576.0),0), "none",d.low/16777216
from master.dbo.sysdevices d, master.dbo.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,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, master.dbo.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 master.dbo.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),(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= value from master..sysconfigures where config=116

while @nextdev != @maxdev /* This is one max device number allowed */
begin
  if exists(select name from master.dbo.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 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
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
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

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

set nocount on

insert into #spdbdesc (dbid)
       select dbid from master.dbo.sysdatabases

set nocount off

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

select @db_size =  ((sum((convert(float,size)) * d.low) / 1048576.0) * 1024.0)
           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 = 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
   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 */

insert into #spt_space (db_size)
select distinct (sum(convert(float,size)) * (convert(float,d.low)) / 1048576.0)
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(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((master.dbo.spt_values.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 = master.dbo.sysdatabases.name,
        db_size = convert(varchar(10),
            round(
            (master.dbo.spt_values.low * convert(float, sum(master.dbo.sysusages.size)))
            / 1048576, 0))
            + " " + "MB",
        owner = master.dbo.syslogins.name,
        dbid = master.dbo.sysdatabases.dbid,
        created = convert(char(11), master.dbo.sysdatabases.crdate),
        status = #spdbdesc.dbdesc
            from master.dbo.sysdatabases, master.dbo.syslogins, master.dbo.sysusages, #spdbdesc,
             master.dbo.    spt_values
        where master.dbo.sysdatabases.dbid = #spdbdesc.dbid
            and master.dbo.sysdatabases.suid = master.dbo.syslogins.suid
            and #spdbdesc.dbid = master.dbo.sysusages.dbid
            and master.dbo.spt_values.type = "E"
            and master.dbo.spt_values.number = 1
        group by #spdbdesc.dbid
        having master.dbo.sysdatabases.dbid = #spdbdesc.dbid
            and master.dbo.sysdatabases.suid = master.dbo.syslogins.suid
            and #spdbdesc.dbid = master.dbo.sysusages.dbid
            and master.dbo.spt_values.type = "E"
            and master.dbo.spt_values.number = 1
        order by master.dbo.sysdatabases.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(
            (master.dbo.spt_values.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

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 4.20.22.1"
go

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

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

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