/*
** %Z% generic/upgrade/%M% %I% %G%
**
** USAGE.SQL
**
** Returns the database usage and segment information to ease
** recreation of user databases
**
** 2/11/92 - created (mgm)
*/
use master
go

/*
** Print out the current segment information
*/
print "Current segment and size usage"
if exists (select * from sysobjects where name = "spt_values"
	and type = "U")
 select 
	name = db_name(sysusages.dbid), 
	segmap, 
	usage = b.name, 
	device_name = sysdevices.name,
	MegaBytes = str(round((a.low * convert(float, size)) / 1048576, 0) + 0.5 , 5,0)
 from sysusages, sysdevices, sysdatabases, spt_values a, spt_values b
 where sysdatabases.dbid = sysusages.dbid
		and sysusages.dbid > 2
		and sysdevices.low <= size + vstart
		and sysdevices.high >= size + vstart - 1
		and sysdevices.status & 2 = 2
		and a.type = "E"
		and a.number = 1
		and b.type = "S"
		and sysusages.segmap & 7 = b.number
  order by 1, lstart
else
 select 
	name = db_name(sysusages.dbid), 
	segmap,
	usage = "",
	device_name = sysdevices.name,
	MegaBytes = (size * @@pagesize) / (1024 * 1024)
  from sysusages, sysdevices, sysdatabases
  where sysdatabases.dbid = sysusages.dbid
		and sysusages.dbid > 2
		and sysdevices.low <= size + vstart
		and sysdevices.high >= size + vstart - 1
		and sysdevices.status & 2 = 2
  order by 1, lstart
go