June 17, 2008 at 11:02 am
Hello All
Is there a way that I can determine is a database, one or all of them are currently online and active. A query would be what I am after.
I want to create an ASP.NET page that lists the databases for a certain server, and be able to show the databases that are online. This will tell me if everything is ok once the server has been bounced.
Thanks
Andrew SQLDBA
June 17, 2008 at 11:26 am
The most effective solution would probably be to display the results of:
EXEC sp_helpdb
June 17, 2008 at 11:33 am
Here is some SQL to get all of the database properties and has been tested with both 2000 and 2005 with various collatations. The SQL is different for SQL Server 7 and, if needed, I can post.
select@@servernameAS SQLServerName
,db.name as DatabaseName
,db.crdateas CreateTs
,db.sidas LoginSid_DBOwner
,suser_sname(db.sid) as LoginName_DBOwner
,db.cmptlevelas SQLServerBuildId
,COALESCE ( cast ( DATABASEPROPERTYEX(db.name,'Collation') as varchar(255) ) , 'UNKNOWN' )as CollationName
,cast ( DATABASEPROPERTYEX(db.name,'Recovery') as varchar(255) ) as RecoveryModeName
,COALESCE ( cast ( DATABASEPROPERTYEX(db.name,'SQLSortOrder') as varchar(255) ) , 'UNKNOWN' )as SQLSortOrderName
,cast ( DATABASEPROPERTYEX(db.name,'Status') as varchar(255) ) as StatusName
,cast ( DATABASEPROPERTYEX(db.name,'Updateability') as varchar(255) ) as UpdateabilityName
,cast ( DATABASEPROPERTYEX(db.name,'UserAccess') as varchar(255) ) as UserAccessName
-- Replication
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsMergePublished') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas MergePublishedInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsSubscribed') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas SubscribedInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsFulltextEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas FulltextEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsInStandBy') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas LogShipTargetInd
,COALESCE ( (select 'Y'
from msdb.dbo.log_shipping_primariesas log_shipping_primaries
wherelog_shipping_primaries.primary_database_name = db.name
) , 'N') as LogShipSourceInd
-- Automatice Behaviors
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoClose') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoCloseInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoCreateStatistics') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoCreateStatisticsInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoShrink') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoShrinkInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoUpdateStatistics') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoUpdateStatisticsInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsRecursiveTriggersEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas RecursiveTriggersEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsTornPageDetectionEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas TornPageDetectionEnabledInd
-- ANSI Behavior
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsNullConcat') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas NullConcatInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiNullDefault') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiNullDefaultInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiNullsEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiNullsEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiPaddingEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiPaddingEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiWarningsEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiWarningsEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsQuotedIdentifiersEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas QuotedIdentifiersEnabledInd
-- Numeric Behavior
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsArithmeticAbortEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas ArithmeticAbortEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsNumericRoundAbortEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas NumericRoundAbortEnabledInd
-- Cursors
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsCloseCursorsOnCommitEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas CloseCursorsOnCommitEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsLocalCursorsDefault') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas LocalCursorsDefaultInd
from master.dbo.sysdatabases db
SQL = Scarcely Qualifies as a Language
June 17, 2008 at 12:19 pm
Thank You
This will work perfectly
Have a good one
Andrew SQLDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply