----------------------------------------------------------------------
--  This SP simply Captures SQL Recovery Information from the local server.
--  This information is Outputted to local 
--  C:\HHJobs\Servername_MSSQL_Recovery_Info.txtand copied to 
--  \\HHNTISDEV01\D$\HH_MSSQL_Recovery_Info
----------------------------------------------------------------------
use msdb
go
IF OBJECT_ID('dbo.sp_HH_MSSQL_Recovery_Info') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_HH_MSSQL_Recovery_Info
END
go
Create procedure sp_HH_MSSQL_Recovery_Info
as
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @dbname VARCHAR(100)

----------------------------------------------------------------------
select @@version, getdate(), @@servername, 'sp_configure'
--
execute sp_configure 'show advanced option', 1
RECONFIGURE
--
execute sp_configure
--
execute sp_configure 'show advanced option', 0
RECONFIGURE
--
set rowcount 20
--
select getdate(), @@servername 'syslogins'
select * from master..syslogins
set rowcount 0
--
select getdate(), @@servername 'sysaltfiles'
select * from master..sysaltfiles
--
select getdate(), @@servername 'sysdatabases'
select * from master..sysdatabases
--
select getdate(), @@servername 'sysdevices'
select * from master..sysdevices
--
select getdate(), @@servername 'syscharsets'
select * from master..syscharsets
 where type = 1001
--
select getdate(), @@servername 'sysconfigures'
select * from master..sysconfigures
--
select getdate(), @@servername 'syscurconfigs'
select * from master..syscurconfigs
--
select getdate(), @@servername 'sysservers'
select * from master..sysservers
--
select getdate(), @@servername 'sysremotelogins'
select * from master..sysremotelogins
--
select getdate(), @@servername 'sysfilegroups'
select * from master..sysfilegroups
--
select getdate(), @@servername 'sysfiles'
select * from master..sysfiles
--
select getdate(), @@servername 'sysfiles1'
select * from master..sysfiles1
--
execute master..sp_helpsort
--
execute master..sp_helpdb
--
----------------------------------------------------------------------
-- Here we begin the looping through all the individual databases. The
-- 'none' value is a mechanism to skip over unused dbid numbers.
----------------------------------------------------------------------
set @dbname = 'none'
SELECT @counter=MAX(dbid) FROM master..sysdatabases
WHILE @counter > 0
BEGIN
 SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
  IF (SELECT @dbname) = 'none'
    BEGIN
     GOTO NEXTONE
    END

 SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter

----------------------------------------------------------------------
-- This EXEC statements performs an sp_helpdb on all databases.
----------------------------------------------------------------------
   EXEC ('master..sp_helpdb ' + '[' + @dbname + ']')
----------------------------------------------------------------------
--  This is the bottom of the loop, we increment the loop counter @counter 
--  and go back to the top of the loop to process another database.
---------------------------------------------------------------------- 
NEXTONE:
SET @counter = @counter - 1
SET @dbname = 'none'
END
go