Retrieve available database names on a server without using sp_helpdb

[Noonoo]

Hi Folks.

I'm sure one of you out there must have come across this problem.

I'd like to put the names of all active databases in a cursor so I can loop through them. They have to be up and running though - not in single-user mode for example - as the query I'm using falls over when trying to access this information. Surely there must be a table somewhere that holds this?

There's sp_helpdb which looks like it might return available databases only. This is a pain though as it's a stored proc and I'll need a temp table or something to specifically get at the db names. I could do this but need a well opimised query.

There's also master.dbo.sysdatabases. It contains db names but no reliable field to restrict results to avialable db's only. The status field is unreliable, for example, autoshrink is reflected in the status instead of single-user mode if both criteria apply.

If anyone has a nice quick efficient query to extract available database names I'd be delighted to hear from them.

Thanks

Bob

 

[Crowley16]

what version are you using?


--------------------
Procrastinate Now!

 

[Noonoo]

SQL Server 2000

 

[LNBruno]

Look up: EXEC sp_MSforeachdb

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[Noonoo]

Hi.

Thanks for the reply.

Done some research. sp_MSforeachdb provides a way of looping through the databases on server and apparently carrying actions on them. I can see uses for this but I need to retrieve just the operable and fully available database names and do stuff with them.

 

[SQLSister]

If sp_helpdb gives you what you want, you could put the data into a temptable and then use that to run from.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

>> This is a pain though as it's a stored proc and I'll need a temp table or something to specifically get at the db names.

Insert/Exec is your friend!

Create Table #Temp(Name sysname, db_size varchar(20), owner sysname, dbid int, Created DateTime, State VarChar(300), Compatibility_level int)

[!]Insert into #Temp Exec sp_helpdb[/!]

Select * From #Temp
Drop Table #Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom