The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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!

asked 04 Apr '11, 16:06

manish12's gravatar image

manish12
16114
accept rate: 0%


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.

permanent link

answered 04 Apr '11, 16:13

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

edited 04 Apr '11, 16:15

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

(04 Apr '11, 16:24) manish12
Replies hidden

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 ?

(04 Apr '11, 16:54) Daz Liquid
Replies hidden

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();

(04 Apr '11, 17:35) manish12

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.

(04 Apr '11, 17:40) Daz Liquid

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 :-)

(04 Apr '11, 18:01) Mark Culp
2

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.

(04 Apr '11, 18:55) Tyson Lewis

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

(05 Apr '11, 03:51) Daz Liquid
showing 2 of 7 show all flat view

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.

permanent link

answered 04 Apr '11, 16:34

Breck%20Carter's gravatar image

Breck Carter
26.7k418577825
accept rate: 20%

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

(04 Apr '11, 16:40) manish12

That's some fine silverware :-)

(04 Apr '11, 16:58) Daz Liquid
Replies hidden

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

(05 Apr '11, 05:43) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 04 Apr '11, 16:06

question was seen: 6,996 times

last updated: 05 Apr '11, 05:43