Create PROC sp_DevScriptor
AS

/*
sp_DevScriptor	- Database Device Scriptor
Author 		- Charley Hanania
Date		- 19th of January 2000
Version		- 1.00
Description	- This stored procedure can be used in
		  conjunction with sp_revdatabase to
		  script important information on the
		  configuration of the SQL Server
		  Database devices and databases, for
		  use in SQL Server recovery situations.
Notes		* This Procedure is to be created in the
		  master database.
		* This Procedure assumes that all devices
		  are created on the same segment. If not
		  do not use this stored procedure.
		* Procedure cleans up expanded devices by
		  amalgamating them and creating them as a
		  sum of all expansions.
*/

SET NOCOUNT ON

/*
	Create temporary table with all important database
	and device information.
*/
select
	DatabaseName = d.name,
	DeviceName = v.name,
	Usage = t.name,
	VDevNumber = convert(int, substring(convert(binary(4), v.low), 1, 1)),
	size_MB = convert (dec(8), str(	(select low from master.dbo.spt_values
			where
				type = 'E'
			and
				number = 1)
		  * convert(dec(15), u.size)/ 1048576,10,2)),
	PhysicalDeviceName = v.phyname
into
	#DevMaker
from
	master.dbo.sysdatabases d,
	master.dbo.sysusages u,
	master.dbo.sysdevices v,
	master.dbo.spt_values t
where
	d.dbid = u.dbid
and
	u.vstart between v.low and v.high
and
	v.status & 2 = 2
and
	d.name in (SELECT NAME FROM SYSDATABASES)
and
	t.type = 'S'
and
	u.segmap & 7 = t.number
order by
	VDevNumber, u.lstart


SELECT
"
DISK INIT
	NAME = '"+DeviceName+"',
	PHYSNAME = '"+PhysicalDeviceName+"',
	VDEVNO = "+convert(varchar(4), VDevNumber)+",
	SIZE = "+convert(varchar(20), SUM(Size_MB) * 512)+"
GO"
FROM
	#DevMaker
WHERE
	VDevNumber between 1 and 125  -- all devices except master and msdb
GROUP BY
	DatabaseName, DeviceName, Usage, VDevNumber, PhysicalDeviceName
HAVING
	count(*) = 1 -- for cases where devices have not been expanded
ORDER BY
	VDevNumber

SELECT
"
DISK INIT
	NAME = '"+DeviceName+"',
	PHYSNAME = '"+PhysicalDeviceName+"',
	VDEVNO = "+convert(varchar(4), VDevNumber)+",
	SIZE = "+convert(varchar(20), SUM(Size_MB) * 512)+"
GO"
FROM
	#DevMaker
WHERE
	VDevNumber between 1 and 125
GROUP BY
	DatabaseName, DeviceName, Usage, VDevNumber, PhysicalDeviceName
HAVING
	count(*) > 1 -- for cases where devices have been expanded, will amalgamate them into 1
ORDER BY
	VDevNumber

DROP table #DevMaker
GO

GRANT EXECUTE on sp_DevScriptor TO Public