SQL query to get all database names

[Former Member]

What sql query can give me name of all databases ? Like for Sqlserver it is SELECT NAME FROM SYS.DATABASES and for Mysql we can use SHOW DATABASES. I need to run this sql via a jdbc call.

Thanks!

[MarkCulp]

Note that unlike some of the other RDBMSes, SQL Anywhere does not have a "master database" so you cannot get a list of all databases that are on a computer.

Once you are connected to a server you can get the list of all databases running on the server to which you are connected by using

select db_name( number ) from sa_db_list();

See the documentation for more information.

[Former Member]

Awesome, thanks Mark, would work for me ! Must say this is thus far the fastest reply I got on any forum 🙂

[former_SQLA_member1694874]

I've tried this on our v10.0.1.3960 databases and all I get is the database I'm connected to, am I missing something obvious ?

[Former Member]

Daz ,combination of these two queries works for me,via jdbc call

1. SELECT * FROM sa_db_list() -> will give you all ids to feed to next query

2. (which is )say in my case Select db_name( 0 ), db_name( 1 ),db_name( 2 ) from sa_db_list();

[former_SQLA_member1694874]

I think Mark's select would do the same but show it in rows rather than columns (and without the risk of you trying to query a database that wasn't in the results from sa_db_list), the problem I have is sa_db_list() is only returning one database.

Unless (as I said before) I'm missing something simple.

[MarkCulp]

The nice thing about this forum is that you can sign up to get an email anytime a new question is ask (or a number of other events). Hence it is easy to "monitor" the forum without actually doing anything since my email client will tell me when I get new email 🙂

[Former Member]

This will only show you databases running on the current engine/server. If you have multiple instances of SQL Anywhere running, they will need to be queried per-server.

[former_SQLA_member1694874]

doh, I'd forgotten you could start them up with one dbsrv, we've always used one database per dbsrv. Thanks Tyson.