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!

[Breck_Carter]

In addition to Mark's SQL query, the dblocate utility can show you everything that's visible on the network:

This command...

"%SQLANY12%bin32dblocate.exe" -dv -o dblocate.txt

produced this file on my workstation...

SQL Anywhere Server Enumeration Utility Version 12.0.1.3298
Server Name           Address
-----------           -------
benchmark             Envy:2638    

   Running database: test
   Running database: ddd001
   Running database: ddd002
   Running database: ddd003
   Running database: ddd004
   Running database: ddd005
   Running database: ddd006
   Running database: ddd007
   Running database: ddd008
   Running database: ddd009
   Running database: ddd010
   Running database: ddd011
   Running database: ddd012
   Running database: ddd013
   Running database: ddd014
   Running database: ddd015
   Running database: ddd016
   Running database: ddd017
   Running database: ddd018
   Running database: ddd019
   Running database: ddd020
   Running database: ddd021
   Running database: ddd022
   Running database: ddd023
   Running database: ddd024
   Running database: ddd025
   Running database: ddd026
   Running database: ddd027
   Running database: ddd028
   Running database: ddd029
   Running database: ddd030
   Running database: ddd031
   Running database: ddd032
   Running database: ddd033
   Running database: ddd034
   Running database: ddd035
   Running database: ddd036
   Running database: ddd037
   Running database: ddd038
   Running database: ddd039
   Running database: ddd040
   Running database: ddd041
   Running database: ddd042
   Running database: ddd043
   Running database: ddd044
   Running database: ddd045
   Running database: ddd046
   Running database: ddd047
   Running database: ddd048
   Running database: ddd049
   Running database: ddd050
   Running database: ddd051
   Running database: ddd052
   Running database: ddd053
   Running database: ddd054
   Running database: ddd055
   Running database: ddd056
   Running database: ddd057
   Running database: ddd058
   Running database: ddd059
   Running database: ddd060
   Running database: ddd061
   Running database: ddd062
   Running database: ddd063
   Running database: ddd064
   Running database: ddd065
   Running database: ddd066
   Running database: ddd067
   Running database: ddd068
   Running database: ddd069
   Running database: ddd070
   Running database: ddd071
   Running database: ddd072
   Running database: ddd073
   Running database: ddd074
   Running database: ddd075
   Running database: ddd076
   Running database: ddd077
   Running database: ddd078
   Running database: ddd079
   Running database: ddd080
   Running database: ddd081
   Running database: ddd082
   Running database: ddd083
   Running database: ddd084
   Running database: ddd085
   Running database: ddd086
   Running database: ddd087
   Running database: ddd088
   Running database: ddd089
   Running database: ddd090
   Running database: ddd091
   Running database: ddd092
   Running database: ddd093
   Running database: ddd094
   Running database: ddd095
   Running database: ddd096
   Running database: ddd097
   Running database: ddd098
   Running database: ddd099
   Running database: ddd100
ddd12                 Envy:49160   

   Running database: ddd12
ruralfinds            DGRK3X31:2638

   Running database: ruralfinds
3 servers, 103 databases found

It is possible for servers to hide from dblocate by starting with the dbsrv12 -sb 0 option.

[Former Member]

Thanks Breck, will keep this one handy too.:)

[former_SQLA_member1694874]

That's some fine silverware 🙂

[Breck_Carter]

@Daz: ah, the joys of Family Tech Support 🙂

[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.