How do I check that a deployment changed a table the same way in QA and production?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A release script ran in QA and in production, or should have. A column added in QA and forgotten in production, a column widened on one side only, an index created in one place and not the other: each of these is a classic cause of "it works in QA" incidents, and none of them shows in the data until something fails.

You will compare a table between QA and production in one command, read its report when a column exists on one side only and when a column is defined differently, then compare the indexes, which the report does not cover, and see in a query plan why a missing index matters. When nothing differs, one line says so.

Short answer

Connect to one side and name the other connection: COMPARE TABLE STRUCTURE CITY WITH WORLD;. For the indexes, run SHOW INDEXES CITY; on each side.

Before you start

The example compares the practice connections WORLD-QA and WORLD (playing production), which do not exist by default: see Set up QA and production copies of WORLD. Every change of this page goes to WORLD-QA, your practice copy, and Clean up undoes it. With WORLD alone, see the variation "WORLD alone".

1. Compare before the deployment

Open the QA copy and compare its CITY table with the one of WORLD:

CONNECT WORLD-QA;
COMPARE TABLE STRUCTURE CITY WITH WORLD;

The two copies start identical, so BroadSQL prints one line:

Table 'CITY' has an identical structure on this connection and 'WORLD'.

2. Simulate the deployment in QA

A release adds a column NOTE to CITY and widens DISTRICT, a CHAR(20) column in the sample, to 40 characters of variable length. Apply it to the QA copy only:

ALTER TABLE CITY ADD COLUMN NOTE VARCHAR(40);
ALTER TABLE CITY ALTER COLUMN DISTRICT SET DATA TYPE VARCHAR(40);

3. Compare again and read the report

Run the same comparison:

COMPARE TABLE STRUCTURE CITY WITH WORLD;

The report has a section per kind of difference, then the count of the columns that match:

Comparing structure of table 'CITY' between this connection and 'WORLD'

Columns only on this connection (not on 'WORLD'):
  NOTE

Columns with a different definition:
  DISTRICT: this connection = CHARACTER VARYING(40) NOT NULL, 'WORLD' = CHARACTER(20) NOT NULL

Structure is identical for 7 column(s).

How to read it:

• Columns only on this connection: NOTE reached QA and not production. A section "Columns only on 'WORLD'" would list the opposite case.
• Columns with a different definition: each line gives the column's type, size and nullability on both sides. A difference in type, size, number of decimals or nullability is reported; the order of the columns is not.
• Identical: the 7 other columns of CITY are defined the same way on both sides.

The table name is tried as typed, in upper case and in lower case on each side, so the other connection can be a product that stores names in another case.

4. Compare the indexes

The report covers columns only. The other half of a deployment check is the indexes, and a missing index is the usual cause of a production query that is slow after a release. Create, in QA, the index the release would add:

CREATE INDEX IDX_CITY_COUNTRYCODE ON CITY(COUNTRYCODE);

List the indexes of CITY on QA. It shows PRIMARY_KEY_1, on ID, and the new IDX_CITY_COUNTRYCODE, on COUNTRYCODE:

SHOW INDEXES CITY;

Then on production, with ENV PROD, which opens the production connection of the same Database Group:

ENV PROD;
SHOW INDEXES CITY;

Production lists PRIMARY_KEY_1 only: that is the gap.

5. See why it matters

Ask production for the plan of a query that filters cities by country. EXPLAIN only reads, so the Production confirmation never asks:

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

H2's plan contains /* PUBLIC.CITY.tableScan */: production reads the whole table. Come back to QA and ask again:

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

The plan of QA names PUBLIC.IDX_CITY_COUNTRYCODE instead. On 4081 rows the difference is invisible; on a large production table it is the difference between milliseconds and minutes.

6. Check every table of the release at once

Put one comparison per table in a Script of the Scripts Library (the scripts folder), and run it from QA after each deployment:

-- @description: Compare the WORLD tables of this connection with production
-- @tags: world, deployment
COMPARE TABLE STRUCTURE COUNTRY WITH WORLD;
COMPARE TABLE STRUCTURE CITY WITH WORLD;
COMPARE TABLE STRUCTURE COUNTRYLANGUAGE WITH WORLD;
COMPARE TABLE STRUCTURE CURRENCY WITH WORLD;
COMPARE TABLE STRUCTURE REGION WITH WORLD;

Connected to WORLD-QA, run it with:

@deploy/compare-world.bsql;

Each table prints its own report, one line when it is identical. There is no single command for keys and indexes: SHOW PK, SHOW FK and SHOW INDEXES are run on each side.

Variations

• A table in another schema: COMPARE TABLE STRUCTURE PUBLIC.CITY WITH WORLD; uses that schema on both sides. Without a schema, each side uses its own current schema.
• Two vendors: the other connection can be of another database type. Type names then differ by nature (VARCHAR2 and VARCHAR), and the report lists them.
• WORLD alone: copy the table into a local database with DUMP CITY TO WORKCOPY.CITY AS H2;, then from WORLD, COMPARE TABLE STRUCTURE CITY WITH WORKCOPY;. A copy made by DUMP keeps the column names and types of the result.
• The data, not the structure: see Find the rows that differ between two databases.
• Counts and reference values: see Run a post-deployment smoke check.

Watch out

• The other connection must be an active connection of the CDF; SHOW GROUP; lists the connections of your Database Group.
• COMPARE TABLE STRUCTURE compares columns only: keys, indexes, constraints and triggers need their own commands on each side.
• A column renamed on one side appears twice: once as "only on this connection", once as "only on" the other.

Clean up

Undo the deployment on the QA copy. On WORLD-QA:

DROP INDEX IDX_CITY_COUNTRYCODE;
ALTER TABLE CITY DROP COLUMN NOTE;
ALTER TABLE CITY ALTER COLUMN DISTRICT SET DATA TYPE CHAR(20);

COMPARE TABLE STRUCTURE CITY WITH WORLD; then prints the one-line identical report again. To start from an exact copy instead, replace samples/WorldDB_QA.mv.db with a new copy of the sample while BroadSQL is closed. The Script can stay in the library.

Related

• Exploring a database
• Connections, Database Groups and Environments
• COMPARE TABLE STRUCTURE, SHOW INDEXES

Commands used

• COMPARE TABLE STRUCTURE
• SHOW INDEXES
• ENV
• SHOW GROUP
• DESCR

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