Question Re: SQL query to get all database names in Technology Q&A

SQL query to get all database names

Former Member — Mon, 04 Apr 2011 16:06:40 GMT

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!

Re: SQL query to get all database names

MarkCulp — Mon, 04 Apr 2011 16:13:29 GMT

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.

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 16:24:51 GMT

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

Re: SQL query to get all database names

Breck_Carter — Mon, 04 Apr 2011 16:34:13 GMT

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.

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 16:40:05 GMT

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

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 16:54:28 GMT

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 ?

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 16:58:21 GMT

That's some fine silverware 🙂

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 17:35:33 GMT

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

SELECT * FROM sa_db_list() -> will give you all ids to feed to next query
(which is )say in my case Select db_name( 0 ), db_name( 1 ),db_name( 2 ) from sa_db_list();

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 17:40:33 GMT

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.

Re: SQL query to get all database names

MarkCulp — Mon, 04 Apr 2011 18:01:37 GMT

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 🙂

Re: SQL query to get all database names

Former Member — Mon, 04 Apr 2011 18:55:32 GMT

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.

Re: SQL query to get all database names

Former Member — Tue, 05 Apr 2011 03:51:52 GMT

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

Re: SQL query to get all database names

Breck_Carter — Tue, 05 Apr 2011 05:43:08 GMT

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