As I know I can get the databases from SQL Server using sys.databases, however it's listing offline databases also. I keep checking for a flag which shows database online/offline status.
Can anyone help me with this ?
I want the databases from SQL Server which are online, just need to ignore the offline ones
You are looking for the state column - MSDN documentation is your friend always!
state: tinyint
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
So if you want only online databases, use
SELECT (columns) FROM sys.databases WHERE state = 0
You need to put where condition which will pick only online databases.
SELECT *
FROM sys.databases db WHERE
db.state = 0
// O for online Check out all states here.
select name, convert(varchar(20),databasepropertyex(name, 'Status')) status
from master.dbo.sysdatabases
order by name
The fact if a database in SQL server is offline or not can be retrieved from the column status in sys.sysdatabases. The following SQL should in theory get you there:
SELECT name FROM sys.sysdatabases WHERE NOT status & 512 = 512;
(I did not test it but you should get the idea.)
See http://msdn.microsoft.com/de-de/library/ms179900(v=sql.90).aspx for details.
On MSSql - Server try the following statement
select state , state_desc from sys.databases
This will show you the current state of the Database
SELECT *
FROM sys.databases db WHERE
db.state = 0 and owner_sid <> 0x01
You can skip system databases, list only user databases
