Exploring a database

These commands show the structure of the database you are connected to. They read the standard metadata every JDBC driver provides, so they work the same way on H2, PostgreSQL, Oracle, SQL Server and any other database, without querying its own catalog tables.

Getting to know an unfamiliar database

You are connected, and the database is new to you: a system you support for the first time, or a schema someone else designed. Work from the outside in, and let each answer tell you where to look next.

What is it? SHOW DBINFOS gives the database product and version, which tells you what SQL dialect to expect. SHOW CONNECTION reminds you which Connection and Environment you are on (see Connections, Database Groups and Environments).
How is it organized? SHOW SCHEMAS lists the schemas; SET SCHEMA <name> makes one current, so later commands and unqualified table names use it.
What does it contain? SHOW TABLES and SHOW VIEWS list the tables and the views. Add a filter when the list is long: SHOW TABLES ORDER%;.
What does a table hold? DESCR <table> gives its columns and types; SHOW PK <table> its key; CNT <table> counts its rows before you select from a large one.
How does it connect to the rest? SHOW FK <table> shows what the table references, and SHOW REFERENCES <table> what references it: together they let you walk the data model one table at a time.
Where is that column? When you know a column name but not its table, FIND COLUMN CUSTOMER_ID searches the whole database; FIND FK and FIND INDEX search keys and indexes by name.

DEVDB> SHOW TABLES ORDER%;
DEVDB> DESCR ORDER_LINE;
DEVDB> SHOW FK ORDER_LINE;
DEVDB> SHOW REFERENCES ORDERS;
DEVDB> FIND COLUMN CUSTOMER;

Each of these commands is described below.

Commands at a glance

Command	Shows
`SHOW SCHEMAS`, `SHOW CATALOGS`	The schemas or catalogs, optionally those whose name contains a text.
`SHOW TABLES`, `SHOW VIEWS`	The tables, or the views (materialized views included), of the current schema, optionally filtered by name (`SHOW TABLES CUSTOM%;`). `SHOW TABLES` lists tables only, never views, synonyms or sequences.
`DESCR <table>`	The columns of a table, in table order, with type, size (`precision,scale` for `DECIMAL` and `NUMERIC`), nullability and default.
`SHOW PK <table>`	The primary key columns of a table.
`SHOW FK <table>`	The foreign keys the table declares: constraint, column, and the table and column each one references.
`SHOW REFERENCES <table>`	The other way round: the foreign keys of other tables that reference this table.
`SHOW INDEXES <table>`	The indexes of a table, their columns in order, and whether each is unique.
`FIND COLUMN <text>`	Every column, in the whole database, whose name contains the text.
`FIND FK <text>`	The foreign keys of the current schema whose constraint, table or column names contain the text. `FIND REFERENCE` is the same command.
`FIND INDEX <text>`	The indexes of the current schema whose name, table or column names contain the text.
`COMPARE TABLE STRUCTURE <table> WITH <connection>`	The differences between the structure of a table here and in another connection.

SHOW commands describe one table you name. FIND commands search for something whose name you only partly know.

Example

DEVDB> SHOW FK ORDER_LINE;
|---------------|-|---------------|----------------|-----------------|
|FK_NAME        |#|COLUMN         |REFERENCES_TABLE|REFERENCES_COLUMN|
|---------------|-|---------------|----------------|-----------------|
|FK_LINE_ORDER  |1|ORDER_ID       |ORDERS          |ID               |
|FK_LINE_PRODUCT|1|PRODUCT_VARIANT|PRODUCT         |VARIANT          |
|FK_LINE_PRODUCT|2|PRODUCT_CODE   |PRODUCT         |CODE             |
|---------------|-|---------------|----------------|-----------------|

The # column gives the position of a column within a foreign key or an index, so the rows of a key or index made of several columns read in order. FIND FK and FIND INDEX always list every column of a key or index they find, even when only one of its columns matched.

How table names are found

DESCR, SHOW FK, SHOW REFERENCES and SHOW INDEXES find the table the same way:

A name can be qualified with its schema: DESCR SALES.ORDERS;. Without a schema, the current schema is used (see SET SCHEMA).
The name is tried as you typed it, then in upper case, then in lower case; when another case matched, BroadSQL says which table it used.
Apart from case, the name must match a table or view exactly: ORDER does not find ORDERS. Use SHOW TABLES ORDER%; to search.

SHOW PK uses the name exactly as typed, without trying other cases.

When a result is empty, BroadSQL says so (for example, that a table declares no foreign keys) rather than printing an empty table. It reports an error when the table does not exist, when the name matches tables in several schemas (qualify it), or when the database's JDBC driver does not provide that kind of information.

Searching with `FIND`

The text given to FIND COLUMN, FIND FK and FIND INDEX is matched anywhere in the name. As in SQL LIKE, % stands for any sequence of characters and _ for any single character. FIND FK and FIND INDEX ignore case and search the tables of the current schema; FIND COLUMN searches the whole database and follows the database's own rules for case.

Completion

With the interactive console, TAB completes table and view names after DESCR, SHOW PK, SHOW FK, SHOW REFERENCES, SHOW INDEXES, DUMP, LOAD, ALL, CNT and COMPARE TABLE STRUCTURE, also after a schema and a dot (sales.o<TAB>), and table and column names inside SQL.

Connections, Database Groups and Environments: opening the database to explore.
Interactive console: TAB completion of tables and columns.
Patterns & List Sources: feeding what you found into the next query.