Member of the LearnItFirst.com Video Training Network | LearnSqlServer.com | SQL SSIS Training | SQL Programming Tutorials |
LearnSqlServer.com Forums LearnSqlServer.com
Welcome Guest Search | New Posts | Members | Log In | Register

Get Status of Database in Resultset format (Instead of sp_dboption) Options
Scott Whigham
Posted: Monday, August 28, 2006 11:31:31 AM


Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX
Code:

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
Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
     
Don't Forget!
LearnItFirst.com
Don't Forget!
LearnSqlServe.com
 
Home | About Us | Support | Contact Us | Privacy | Site Map | Blogs Blogs Refer a Friend and Get a Free Subscription!
© Copyright 2004-2007 LearnItFirst.com LLC. All rights reserved. All trademarks remain the property of their respective owners.
This site is not affiliated in any way with the Microsoft Corporation.