Get Databases available in Server

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

General SQL Server Forums

New to SQL Server Programming

Get Databases available in Server

Author

Topic

learnerplates
Starting Member

8 Posts

Posted - 2007-12-07 : 09:31:45

I'm trying to list off all the Databases available in a Server, preferrably a sql query which is provider independant e.g. using INFORMATION_SCHEMA

I've tried using 'select * from INFORMATION_SCHEMA.SCHEMATA' on the servers master db but it only returns a list of entries all of which are 'master'.

How can this be done?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-07 : 09:37:09

Make use of system stored procedure - sp_databases

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

learnerplates
Starting Member

8 Posts

Posted - 2007-12-07 : 11:41:18

I don't think stored procedures are an option as this is provider independant, I'm presuming the Stored procedure is provider specific!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 11:45:42

Works for me

SELECT	CATALOG_NAME
FROM	MASTER.INFORMATION_SCHEMA.SCHEMATA

E 12°55'05.25"
N 56°04'39.16"

learnerplates
Starting Member

8 Posts

Posted - 2007-12-07 : 11:56:46

Thanks that's exactly what I first tried and I just get a list with values 'master' !

master
master
master
master
master
master
master
master
master
master
master

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-07 : 12:02:51

SQL Server?
What version?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-07 : 23:28:34

What kind of permission do you have on the server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 01:45:42

quote:
Originally posted by Peso

Works for me
SELECT CATALOG_NAME
FROM MASTER.INFORMATION_SCHEMA.SCHEMATA

E 12°55'05.25"
N 56°04'39.16"

It works only in SQL Server 2000 and returns master in 2005

Madhivanan

Failing to plan is Planning to fail

learnerplates
Starting Member

8 Posts

Posted - 2007-12-10 : 05:17:05

The DB is on 2005.

Is this a 2005 bug?

Aren't these INFORMATION_SCHEMA views supposed to be server independant?

Is there another way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 06:10:23

What do you mean by "Server independant"?
Are you querying SQL Server 2005 from another platform via linked server?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 06:12:56

Ok!
Time to read Books Online, article Breaking Changes to Database Engine Features in SQL Server 2005 and section System Objects and Metadata.

quote:
In earlier versions of SQL Server, the INFORMATION_SCHEMA.SCHEMATA view returned all databases in an instance of SQL Server. In SQL Server 2005, the view returns all schemas in a database. This behavior complies with the SQL Standard. For more information, see SCHEMATA (Transact-SQL).

It seems SQL Server 2005 is correct!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 06:25:29

Try this for SQL Server 2005

SELECT *
FROM MASTER.SYS.DATABASES

E 12°55'05.25"
N 56°04'39.16"

learnerplates
Starting Member

8 Posts

Posted - 2007-12-10 : 08:27:25

yes that works.
SELECT name
FROM MASTER.SYS.DATABASES

The only problem is that I'm writing an application which among other things will try to list off the databases on a server, much like the Microsoft Data Access wizard does. If the database is not SQL Server 2005 then this query is not of any use, I'm looking for a SQL one which will work on all providers.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-10 : 08:31:29

Well, why not do a test on @@version first, then you can use both?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 08:42:39

SELECT SERVERPROPERTY('ProductVersion')

E 12°55'05.25"
N 56°04'39.16"

learnerplates
Starting Member

8 Posts

Posted - 2007-12-10 : 08:45:39

Testing on all other providers is not an option so it's really pointless, there's no point in testing on subset if the user could input any provider, that's why there are standards out there isn't it!

I'm a novice at this DB/SQL stuff so if anyone has another query which works across all that'd be great otherwise I'll try the INFORMATION_SCHEMA.schmata first and if it returns rubbish I'll the MASTER.SYS.DATABASES

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 08:47:53

Refer http://msdn2.microsoft.com/en-us/library/ms143359.aspx

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-10 : 10:30:53

A while ago I did an app in VB which should work against any database using the ado openschema method.
http://www.nigelrivett.net/VB/ADOScema.html
The databases are obtained via adSchemaCatalogs
It also gets the list of tables, proceures and procedure parameters.
Don't know if that might help.
Not sure if I tested it against v2005.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pootle_flump

1064 Posts

Posted - 2007-12-10 : 11:26:52

I am not sure but I think the standard restricts the scope of information_schema to the database level.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-10 : 12:09:47

there is no query for this that will work against all DBMS. if all you care about is 2000/2005, then this will work on both:

select name from master.dbo.sysdatabases

if you care about oracle, mysql, and the rest of them, you'll have to use different queries for each of those.

elsasoft.org

learnerplates
Starting Member

8 Posts

Posted - 2007-12-11 : 10:52:07

Thanks folks, I'll look further afield for a non SQL Server solution.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources