if exists (select * from sysobjects where id = 
object_id('dbo.sp_servermap') and sysstat & 0xf = 4)
drop procedure dbo.sp_servermap
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 containg the letters of the
desired report.

This procedure was developed on a 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.
*/
--clare @selection varchar(10) select @selection  = "ABCDEF"
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 "Current Date/Time" = getdate()

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,dev.name,usg.vstart

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),
"status   " = 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 & 1024)/1024+1,1) +
substring(" G",(status & 2048)/2048+1,1) +
substring(" H",(status & 4096)/4096+1,1) +
substring(" I",(status & 16384)/16384+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 ,db.name ,db.status,db.crdate,db.dumptrdate

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      db is read-only"
print " G      db is for dbo use only"
print " H      db in single-user mode"
print " I      db name has been changed"
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),
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 vstart between low and high
and cntrltype = 0
and db.dbid = usg.dbid
order by dev.name,usg.vstart

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("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 ,low ,high,status ,cntrltype
having cntrltype=0
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