How do I explore an unfamiliar database in fifteen minutes?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

You have been asked to support an application whose database you have never opened. Before you can answer a single question about it, you need a first map: which product and version it runs, which schemas and tables it holds, how big they are, how the important tables are built and how they relate.

You will explore WORLD as if you had never seen it, in the order that answers those questions: the database, its schemas, its tables and their sizes, the structure of a table, a column found by part of its name, the keys and indexes, one wide row read comfortably, and a quick SELECT * turned into an explicit query you can keep. Every command of this page only reads.

Short answer

SHOW DBINFOS;, SHOW SCHEMAS;, SHOW TABLES;, CNT <table>;, DESCR <table>;, then FIND COLUMN <text>;, SHOW PK, SHOW FK and SHOW INDEXES for the tables that matter.

1. Which database is it?

Connect to the sample. CONNECT already prints the connection, the JDBC driver and the database product with its version; SHOW DBINFOS prints them again whenever you need them:

CONNECT WORLD;
SHOW DBINFOS;

For WORLD, the product is H2. On another database, this is where you learn that you are on PostgreSQL 16 or Oracle 19c, which decides the SQL dialect of everything that follows.

2. Which schemas, which tables?

List the schemas first. The current schema is flagged, and it is the one that table names without a schema refer to:

SHOW SCHEMAS;

WORLD has three: PUBLIC, the current one, where the data lives; INFORMATION_SCHEMA, the catalog that describes the database itself; and FT, which H2 creates for full-text search. On an unfamiliar database, a schema you cannot explain is a question for the application team.

Then list the tables of the current schema:

SHOW TABLES;

WORLD lists ten tables. Five of them are the sample's data: CITY, COUNTRY, COUNTRYLANGUAGE, CURRENCY and REGION. The five others, INSTANCE, LANDSCAPE, PERSISTENT_LOGINS, TEST and TYPE, are not described by any documentation: a real database often holds such tables, and they are worth noting before you rely on the list. To narrow the list, give a pattern; this one lists COUNTRY and COUNTRYLANGUAGE:

SHOW TABLES COUNTRY%;

3. How big is each table?

Count before you select. On a production table, a SELECT * without a filter can return millions of rows; CNT tells you how many there are first:

CNT CITY;
CNT COUNTRY;
CNT REGION;

WORLD answers 4081, 254 and 4410. CNT counts exactly, as SELECT COUNT(*) would, so on a very large table it takes as long as that query.

4. How is a table built?

DESCR lists the columns of a table in table order, with their types, sizes and nullability:

DESCR COUNTRY;

COUNTRY has 25 columns. Note the type of NAME, CHARACTER(52): a fixed-length text column, whose values are padded with spaces. Comparisons and exports of such columns work best on the trimmed value, TRIM(NAME), which is why the examples of these how-tos use it.

5. Where is that column?

A column name from an error message or a report is often all you know. FIND COLUMN searches every column name of the database; % stands for any sequence of characters and _ for one character, as in SQL LIKE:

FIND COLUMN COUNTRY%CODE;

It finds COUNTRYCODE in CITY and in COUNTRYLANGUAGE, and COUNTRY_CODE2 in REGION: three tables, two spellings, one search.

6. Keys and relationships

The primary key tells you what identifies a row. COUNTRY is identified by CODE, COUNTRYLANGUAGE by two columns, COUNTRYCODE and LANGUAGE:

SHOW PK COUNTRY;
SHOW PK COUNTRYLANGUAGE;

The foreign keys tell you how tables relate. SHOW FK COUNTRY; shows that COUNTRY.CURRENCY_CODE refers to CURRENCY.CODE, and SHOW FK REGION; that REGION.COUNTRY_CODE2 refers to COUNTRY.CODE2:

SHOW FK COUNTRY;
SHOW FK REGION;

CITY holds a country code too, yet declares no foreign key: for the database, CITY and COUNTRY are unrelated. Check what depends on a table before you change it shows what that means in practice.

7. Indexes, and why a query is slow

Indexes decide which queries are fast. SHOW INDEXES lists them with their columns, UNIQUE or NON-UNIQUE:

SHOW INDEXES CITY;
SHOW INDEXES COUNTRY;

CITY has one index, the primary key on ID. COUNTRY has its primary key on CODE, a unique index on CODE2 and an index on CURRENCY_CODE. To see what that means for a query, ask the database for its plan. A query that filters cities by country has no index to use:

EXPLAIN SELECT ID, NAME FROM CITY WHERE COUNTRYCODE = 'FRA';

H2 answers with a plan that contains /* PUBLIC.CITY.tableScan */: it reads the whole table. The same question on COUNTRY.CODE2, EXPLAIN SELECT CODE FROM COUNTRY WHERE CODE2 = 'FR';, names COUNTRY_UNIQUE_CODE2_INDEX_6 instead. On a large schema, FIND INDEX finds the indexes whose name, table or columns contain a text:

FIND INDEX CODE2;

8. Read one wide row

COUNTRY has 25 columns, too many to read across a screen. SET LIST ON shows each row as a form, one column per line:

SET LIST ON;
SELECT * FROM COUNTRY WHERE CODE = 'FRA';
SET LIST OFF;

SET LIST OFF goes back to the table layout for the following queries.

9. Turn the quick query into one you can keep

The SELECT * you just ran is fine for a look, not for a report: its columns change when the table changes. EXPAND replaces the * of the last query with the real column names, and FORMAT lays the query out on several lines. Neither runs the query; // shows the result of the rewrite:

SELECT * FROM COUNTRY WHERE CODE = 'FRA';
EXPAND;
FORMAT;
//

The last query now names the 25 columns, one per line. Delete the ones you do not need, or run it as it is with /.

10. Keep the map

H2's catalog estimates the number of rows of every table. Writing that list to a Markdown file gives you a first map to keep with your notes:

DUMP (SELECT TABLE_NAME, ROW_COUNT_ESTIMATE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ORDER BY TABLE_NAME) TO world_map AS MD;

The file world_map.md is written in the export folder (the DefaultFolder setting). Catalog views differ from one product to another: on PostgreSQL, Oracle or SQL Server, use that product's own catalog, or a list of CNT results.

Variations

• Another schema: SET SCHEMA <schema>;, then the same commands. SHOW TABLES and DESCR then use that schema.
• Views: SHOW VIEWS; lists the views of the current schema.
• Another product: the commands of this page are the same on every database; see Connect to your database.
• A table someone mentioned by part of its name: SHOW TABLES LANG; lists the tables whose name contains LANG.

Watch out

• FIND COLUMN searches the whole database and follows the database's own rules for case. FIND FK and FIND INDEX ignore case and search the current schema only.
• A short search text such as ID returns many rows: add a few letters.
• EXPAND refuses a query whose source is a subquery, and a bare * over several joined tables: write alias.* there.
• CNT and SELECT * run on the database like any query: on a busy production database, prefer the catalog's estimates for the first look.

Clean up

Delete world_map.md from the export folder when you no longer need it.

Related

• Exploring a database
• Check what depends on a table before you change it
• Find orphan rows, duplicates and missing values
• Interactive console

Commands used

• SHOW DBINFOS
• SHOW SCHEMAS
• SHOW TABLES
• CNT
• DESCR
• FIND COLUMN
• SHOW PK
• SHOW FK
• SHOW INDEXES
• FIND INDEX
• SET LIST
• EXPAND
• FORMAT

Found something wrong or unclear on this page? Tell us, with the page address.