use sybsystemprocs
go
/*
 * DROP PROC dbo.sp_servermap
 */
IF OBJECT_ID('dbo.sp_servermap') IS NOT NULL
BEGIN
    DROP PROC dbo.sp_servermap
    PRINT '<<< DROPPED PROC dbo.sp_servermap >>>'
END
go

create proc sp_servermap (@selection varchar(10) = "ABCDEF")
as

/* produces 6 "reports" against all possible data in
   master..sysdatabases
   master..sysdevices
   master..sysusages

   sp_servermap help
   produces a list of the six reports.
   A subset of the complete set of reports can be requested by passing
   an argument that consists of a string containing the letters of the
   desired report.

   This procedure was developed on 4.9.1 server. It will run on 4.8
   and 10.0 servers, but it has not been verified that the results
   produced are correct.
*/

declare @atitle varchar(40),
        @btitle varchar(40),
        @ctitle varchar(40),
        @dtitle varchar(40),
        @etitle varchar(40),
        @ftitle varchar(40),
        @stars varchar(40),
        @xstars varchar(40)

set nocount on

select @atitle = "A - DATABASE SEGMENT MAP",
       @btitle = "B - DATABASE INFORMATION",
       @ctitle = "C - DEVICE ALLOCATION MAP",
       @dtitle = "D - DEVICE NUMBER, DEFAULT & SPACE USAGE",
       @etitle = "E - DEVICE LOCATION",
       @ftitle = "F - MIRRORED DEVICES",
       @selection = upper(@selection),
       @stars = replicate("*",40)

if @selection = "HELP" begin
  print @atitle
  print @btitle
  print @ctitle
  print @dtitle
  print @etitle
  print @ftitle
  print ""
  print "select any combination of reports by entering a string of"
  print "report letters as the argument to sp_servermap:"
  print "      sp_servermap acd"
  print "will select reports A,C and D."
  print "calling sp_servermap with no argument will produce all reports"
 return
 end

select @@servername, "Current Date/Time" = getdate()
select "Version" = @@version

if charindex("A",@selection) > 0
begin
print ""
print @atitle
select @xstars = substring(@stars,1,datalength(@atitle))
print @xstars

select db=substring(db.name,1,15),db.dbid,
       usg.segmap,
       segs = substring(" U",sign(usg.segmap/8)+1,1) +
              substring(" L",(usg.segmap & 4)/4+1,1) +
      substring(" D",(usg.segmap & 2)/2+1,1) +
              substring(" S",(usg.segmap & 1)+1,1),
       "device fragment"=substring(dev.name,1,15),
       "start (pg)" = usg.vstart,"size (MB)" = str(usg.size/512.,7,2)
from master.dbo.sysusages usg,
     master.dbo.sysdevices dev,
     master.dbo.sysdatabases db
where vstart between low and high
  and cntrltype = 0
  and db.dbid = usg.dbid
order by db.dbid, usg.lstart

print ""
print"Segment Codes:"
print "U=User-defined segment on this device fragment"
print "L=Database Log may be placed on this device fragment"
print "D=Database objects may be placed on this device fragment by DEFAULT"
print "S=SYSTEM objects may be placed on this device fragment"
print ""
end

if charindex("B",@selection) > 0
begin
print ""
print @btitle
select @xstars = substring(@stars,1,datalength(@btitle))
print @xstars

select db=substring(db.name,1,15),
       db.dbid,
       "size (MB)" = str(sum(usg.size)/512.,7,2),
       "db status codes " = substring(" A",(status & 4)/4+1,1) +
                     substring(" B",(status & 8)/8+1,1) +
                     substring(" C",(status & 16)/16+1,1) +
                     substring(" D",(status & 32)/32+1,1) +
                     substring(" E",(status & 256)/256+1,1) +
                     substring(" F",(status & 512)/512+1,1) +
                     substring(" G",(status & 1024)/1024+1,1) +
                     substring(" H",(status & 2048)/2048+1,1) +
                     substring(" I",(status & 4096)/4096+1,1) +
                     substring(" J",(status & 16384)/16384+1,1) +
                     substring(" K",(status & 64)/64+1,1) +
                     substring(" L",(status & 128)/128+1,1) +
                     substring(" M",(status2 & 1)/1+1,1) +
                     substring(" N",(status2 & 2)/2+1,1) +
                     substring(" O",(status2 & 4)/4+1,1) +
                     substring(" P",(status2 & 8)/8+1,1) +
                     substring(" Q",(status2 & 16)/16+1,1) +
                     substring(" R",(status2 & 32)/32+1,1),
       "created" = convert(char(9),crdate,6) + " " +
                   convert(char(5),crdate,8),
       "dump tran" = convert(char(9),dumptrdate,6) + " " +
                 convert(char(5),dumptrdate,8)
from master.dbo.sysdatabases db,
     master.dbo.sysusages usg
where db.dbid =usg.dbid
group by db.dbid
order by db.dbid

print ""
print "Status Code Key"
print ""
print "Code       Status"
print "----       ----------------------------------"
print " A         select into/bulk copy allowed"
print " B         truncate log on checkpoint"
print " C         no checkpoint on recovery"
print " D         db in load-from-dump mode"
print " E         db is suspect"
print " F         ddl in tran"
print " G         db is read-only"
print " H         db is for dbo use only"
print " I         db in single-user mode"
print " J         db name has been changed"
print " K         db is in recovery"
print " L         db has bypass recovery set"
print " M         abort tran on log full"
print " N         no free space accounting"
print " O         auto identity"
print " P         identity in nonunique index"
print " Q         db is offline"
print " R         db is offline until recovery completes"
print ""
end

if charindex("C",@selection) > 0
begin
print ""
print @ctitle
select @xstars = substring(@stars,1,datalength(@ctitle))
print @xstars

select "device fragment"=substring(dev.name,1,15),
       "start (pg)" = usg.vstart,"size (MB)" = str(usg.size/512.,7,2),
       db=substring(db.name,1,15),
       lstart,
       segs = substring(" U",sign(usg.segmap/8)+1,1) +
              substring(" L",(usg.segmap & 4)/4+1,1) +
              substring(" D",(usg.segmap & 2)/2+1,1) +
              substring(" S",(usg.segmap & 1)+1,1)
from master.dbo.sysusages usg,
     master.dbo.sysdevices dev,
     master.dbo.sysdatabases db
where usg.vstart between dev.low and dev.high
  and dev.cntrltype = 0
  and db.dbid = usg.dbid
group by dev.name, usg.vstart, db.name
having db.dbid = usg.dbid
order by dev.name, usg.vstart


print ""
print "Segment Codes:"
print "U=USER-definedsegment on this device fragment"
print "L=Database LOG may be placed on this device fragment"
print "D=Database objects may be placed on this device fragment by DEFAULT"
print "S=SYSTEM objects may be placed on this device fragment"
print ""
end

if charindex("D",@selection) > 0
begin
print ""
print @dtitle
select @xstars = substring(@stars,1,datalength(@dtitle))
print @xstars

declare @vsize int
select @vsize = low
from master.dbo.spt_values
where type="E"
   and number = 3

select device = substring(name,1,15),
       vdevno = convert(tinyint,substring(convert(binary(4),low),@vsize,1)),
       "default disk?" = "    " + substring("NY",(status & 1)+1,1),
       "total (MB)" = str(round((high-low)/512.,2),7,2),
       used = str(round(isnull(sum(size),0)/512.,2),7,2),
       free = str(round(abs((high-low-isnull(sum(size),0))/512.),2),7,2)
from master.dbo.sysusages,
     master.dbo.sysdevices
where vstart between low and high
 and cntrltype=0
 group by all name
    having cntrltype=0
order by vdevno
end

if charindex("E",@selection) > 0
begin
print ""
print @etitle
select @xstars = substring(@stars,1,datalength(@etitle))
print @xstars

select device = substring(name,1,15),
       location = substring(phyname,1,60)
from master.dbo.sysdevices
where cntrltype=0
end

if charindex("F",@selection) > 0
begin
if exists (select 1
           from master.dbo.sysdevices
           where status & 64 = 64)
begin

print ""
print @ftitle
select @xstars = substring(@stars,1,datalength(@ftitle))
print @xstars

select device = substring(name,1,15),
       pri =" " + substring("* **",(status/256)+1,1),
       sec = " " + substring(" ***",(status/256)+1,1),
       serial = "   " + substring(" *",(status & 32)/32+1,1),
       "mirror" = substring(mirrorname,1,35),
       reads = "   " + substring(" *",(status & 128)/128+1,1)
from master.dbo.sysdevices
where cntrltype=0
 and status & 64 = 64
end
else
begin
print ""
print "NO DEVICES ARE MIRRORED"
end
end

set nocount off


go
IF OBJECT_ID('dbo.sp_servermap') IS NOT NULL
BEGIN
    PRINT '<<< CREATED PROC dbo.sp_servermap >>>'
    grant execute on dbo.sp_servermap to sa_role
END
ELSE
    PRINT '<<< FAILED CREATING PROC dbo.sp_servermap >>>'
go