LearnItFirst User Forum

use master
go
IF ( SELECT OBJECTPROPERTY ( OBJECT_ID ( 'sp_GetDBStatus' ), 'IsProcedure') ) = 1
DROP PROC sp_GetDBStatus
go
CREATE PROC sp_GetDBStatus (@dbname SYSNAME)
AS
/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This gives a normalized resultset of the "Status" column from running sp_dboption. I hate having to scroll through sp_helpdb's Status column
      or SELECT DATABASEPROPERTYEX for each item to check the database's options so know I don't have to do that anymore!

    Misc Notes: Modified July, 2006 to include the new properties for SQL Server 2005. If you run this on SQL Server 2000 and see NULL in any columns
        it is because that option is not available for SQL Server 2000.

    Other Notes: This was adapted FROM sp_helpdb in 2001 and really not modified much...
   
    Versions: SQL Server 2005, 2000
   
    Creation Date: August 28, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
DECLARE @DynamicSql NVARCHAR(625), @name SYSNAME, @cmd NVARCHAR(279), @propdesc VARCHAR(40)
SET NOCOUNT ON

DECLARE @ReturnTable TABLE ( dbid INT NULL, dbname SYSNAME NULL, dbdesc NVARCHAR(600) NULL)

-- See the database EXISTS
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
WHERE (@dbname IS NULL or name = @dbname))
BEGIN
  RAISERROR(15010,-1,-1,@dbname)
  RETURN (1)
END

INSERT @ReturnTable (dbid,dbname)
  SELECT dbid,name FROM master.dbo.sysdatabases WHERE (@dbname IS NULL or name = @dbname)

-- Now for each dbid in @ReturnTable, build the database status description.

DECLARE @CurrentDbId SMALLINT /* the one we're currently working on */
SELECT @CurrentDbId = min(dbid) FROM @ReturnTable
WHILE @CurrentDbId IS NOT NULL
BEGIN
SET @name = db_name(@CurrentDbId)

-- These properties always available
  INSERT @ReturnTable SELECT @CurrentDbId,@name, 'Status=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'Status'))
  INSERT @ReturnTable SELECT @CurrentDbId,@name, 'Updateability=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'Updateability'))
  INSERT @ReturnTable SELECT @CurrentDbId,@name, 'UserAccess=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'UserAccess'))
  INSERT @ReturnTable SELECT @CurrentDbId,@name, 'Recovery=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'Recovery'))
  INSERT @ReturnTable SELECT @CurrentDbId,@name, 'Version=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'Version'))

-- These props only available IF db not shutdown
IF DatabaseProperty(@name, 'IsShutdown') = 0
BEGIN
    INSERT @ReturnTable SELECT @CurrentDbId,@name, 'Collation=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'Collation'))
    INSERT @ReturnTable SELECT @CurrentDbId,@name, 'SQLSortOrder=' + CONVERT(SYSNAME,DATABASEPROPERTYEX(@name,'SQLSortOrder'))
END

-- These are the boolean properties
IF DATABASEPROPERTYEX(@name,'IsAutoClose') = 1
   INSERT @ReturnTable SELECT @CurrentDbId,@name, 'IsAutoClose'
 
IF DATABASEPROPERTYEX(@name,'IsAutoShrink') = 1
   INSERT @ReturnTable SELECT @CurrentDbId,@name,  'IsAutoShrink'
 
IF DATABASEPROPERTYEX(@name,'IsInStandby') = 1
   INSERT @ReturnTable SELECT @CurrentDbId,@name, 'IsInStandby'
 
IF DATABASEPROPERTYEX(@name,'IsTornPageDetectionEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsTornPageDetectionEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsAnsiNULLDefault') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsAnsiNULLDefault' )
 
IF DATABASEPROPERTYEX(@name,'IsAnsiNULLsEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsAnsiNULLsEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsAnsiPaddingEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsAnsiPaddingEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsAnsiWarningsEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsAnsiWarningsEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsArithmeticAbortEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsArithmeticAbortEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsAutoCreateStatistics') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsAutoCreateStatistics' )
 
IF DATABASEPROPERTYEX(@name,'IsAutoUpdateStatistics') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsAutoUpdateStatistics' )
 
IF DATABASEPROPERTYEX(@name,'IsCloseCursorsOnCommitEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsCloseCursorsOnCommitEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsFullTextEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsFullTextEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsLocalCursorsDefault') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsLocalCursorsDefault' )
 
IF DATABASEPROPERTYEX(@name,'IsNULLConcat') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsNULLConcat' )
 
IF DATABASEPROPERTYEX(@name,'IsNumericRoundAbortEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsNumericRoundAbortEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsQuotedIdentifiersEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsQuotedIdentifiersEnabled' )
 
IF DATABASEPROPERTYEX(@name,'IsRecursiveTriggersEnabled') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsRecursiveTriggersEnabled' )

IF DATABASEPROPERTYEX(@name,'IsMergePublished') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsMergePublished' )

IF DATABASEPROPERTYEX(@name,'IsPublished') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsPublished'  )

IF DATABASEPROPERTYEX(@name,'IsSubscribed') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsSubscribed' )

IF DATABASEPROPERTYEX(@name,'IsSyncWithBackup') = 1
   INSERT @ReturnTable VALUES (@CurrentDbId,@name, 'IsSyncWithBackup'  )

SELECT dbname, dbdesc FROM @ReturnTable WHERE dbdesc is not NULL

DELETE @ReturnTable WHERE dbid = @CurrentDbId

SELECT @CurrentDbId = min(dbid) FROM @ReturnTable
END
GO
EXEC sp_GetDBStatus master