Database Status - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Status

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-22 : 11:12:28

Hello

I would like to know a script , which gives the status of database either online or offline. I need just this parameter. I donot want to use the sp_helpdb.

Thanks

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-08-22 : 11:26:49

You can do like this:

SELECT DatabasePropertyEx('Your_DB_Name','Status')

Thanks

Sanjeev Shrestha
12/17/1971

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-22 : 12:53:46

Thanks for the syntax.

Is there any script , that I can use on the master db , and shows me which databases are online and which are offline

Thanks

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-08-22 : 13:14:30

You can do some thing like this:
GO
USE MASTER

DECLARE @dbname VARCHAR(100)

DECLARE dbname_cursor CURSOR FOR
SELECT name
FROM sysdatabases

OPEN dbname_cursor

FETCH NEXT FROM dbname_cursor
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @dbname AS DBName,
Databasepropertyex(@dbname,'Status') AS Status
FETCH NEXT FROM dbname_cursor
INTO @dbname
END

CLOSE dbname_cursor

DEALLOCATE dbname_cursor

Sanjeev Shrestha
12/17/1971

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources