List tables in current schema as a select

[Jacob Qvortrup]

Hi.

I an attempt to migrate from MSSQL Server to H2 i need to be able to query the available tables in the current schema.

My use case is as follows:
I have a JDBC connection where the command "set schema ABC" have been executed.
I want to know if a particullar table is present in the current schema by doing a query and give the ResultSet to the original code
The code will then call "rs.next()" to determine if a row was returned (the content of the row is not read, just the fact that its there)

In MSSQL Server we use databases instead of schemas so this is done using the query:
SELECT name FROM sysobjects WHERE name = 'table'

My problem is that using INFORMATION_SCHEMA means i need to know the name of the schema (which i don't at the time)

Anyone?

/Jacob

[Noel Grandin]

Use the JDBC metadata feature.

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/4egpcpUqGOAJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

The JDBC database metadata functions are the best solution.

If you need to access INFORMATION_SCHEMA directly, you could use the SCHEMA() function: http://h2database.com/html/functions.html#schema

Regards,

Thomas

--

[Jacob Qvortrup]

Hi again.

Yes that worked. I must have been blind or something because i have looked at the SCHEMA() function together with the INFORMATION_SCHEMA, but was unable to make it work. Its as simple as this:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME LIKE 'TEST%'

I redraws my patch for the SHOW TABLE command that i have posted in another thread.

Thank you for the help.

/Jacob