How to view current schema search path in a h2 database?

Question

When connecting to an h2 database, the default search path is set to public. You can alter it with the SET SCHEMA_SEARCH_PATH.

But if you alter it multiple times, at some point how can you view its current content?

I wasn't able to find some kind of commands like this VIEW SCHEMA_SEARCH_PATH or SHOW SCHEMA_SEARCH_PATH.

Typing them give me error messages.

score 2 · Accepted Answer · answered Apr 13 '12 at 13:36

2

It's a bit a hack, but you could use:

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATE 
WHERE KEY='SCHEMA_SEARCH_PATH'

answered Apr 13 '12 at 13:36

Thomas Mueller

It's really a hack but it does the job pretty well ^^ – Stephan Apr 13 '12 at 13:41

score 0 · Answer 2 · answered Mar 01 '17 at 14:47

0

Non-hack solution: SELECT SCHEMA()

answered Mar 01 '17 at 14:47

Serge

I tried connecting with this url: `jdbc:h2:mem:;INIT=create schema if not exists po;SCHEMA_SEARCH_PATH=PO,INFORMATION_SCHEMA,PUBLIC`. `SELECT SCHEMA()` returned only PUBLIC whereas Thomas' solution printed the expected schema search_path. **h2 1.4.193** – Stephan Mar 01 '17 at 15:25
Agreed, search path isn't the same as current schema and may include multiple schemas. Current schema is important when you execute some DDL, for SELECTs search_path is more relevant. – Serge Mar 01 '17 at 21:47

2 Answers2