USE master
GO

IF OBJECT_ID('sp_ABhelp_revdevice') IS NOT NULL
        DROP PROCEDURE sp_ABhelp_revdevice
GO

CREATE PROCEDURE sp_ABhelp_revdevice (@devicename varchar(30) = '%')
as
/* 12/19/97  VRI  This procedure will reverse-engineer the needed DISK INIT statements
                  for use in the case of a catastraphic data loss.  Should be used 
                  along with sp_help_revdatabase (from MS) and database dumps to 
                  ensure recreatability of SQL backups.
*/

SELECT 'DISK INIT ' + char(13) + char(10) + 
       '  NAME = "' + d.name + '", ' + char(13) + char(10) + 
       '  PHYSNAME = "' + phyname + '", ' + char(13) + char(10) + 
       '  VDEVNO = ' + convert(varchar(3), convert(tinyint, substring(convert(binary(4), d.low), v.low, 1))) + ',' + char(13) + char(10) + 
       '  SIZE = ' + convert(varchar(10), convert(int, round(((d.high - d.low + 1) * convert(float, v2.low)) / 2048, 0))) + 
            '   -- ' + convert(varchar(10), convert(int, round(((d.high - d.low + 1) * convert(float, v2.low)) / 1048576, 0))) + ' MB' +
          char(13) + char(10) + char(13) + char(10)
  FROM sysdevices d, 
       master.dbo.spt_values v, 
       master.dbo.spt_values v2
  WHERE d.status & 2 = 2 
    AND v2.number = 1
    AND v2.type = 'E'
    AND v.number = 3
    AND v.type = 'E'
    AND d.name like @devicename
  ORDER BY substring(convert(binary(4), d.low), v.low, 1)
GO

GRANT EXECUTE ON sp_ABhelp_revdevice TO public
GO