Generate SQL and notebooks

Generate SQL for one object

Sometimes you need SQL for one object, not the whole schema: a CREATE TABLE to paste into a ticket, or a single view to deploy to a sandbox. Every table has a SQL tab that generates this for you in any dialect. Views, procedures, and functions keep their own SQL on their SQL tab.

In the app: Data Model › Schemas › your schema › Tables › a table › SQL tab

[screenshot]
A table's SQL tab with the dialect picker open, the Use IF (NOT) EXISTS checkbox, and the CREATE TABLE Statement card.

Get SQL for a table

  1. Open the table and click the SQL tab.
  2. The tab opens in the schema’s default dialect. The current dialect shows next to SQL Dialect:, with a short description underneath.
  3. To see another dialect, pick it from the dropdown. The statements regenerate straight away.
  4. Tick or untick Use IF (NOT) EXISTS to add or remove existence guards. It is on by default.
  5. Click Copy on any card to put that statement on your clipboard.

The table’s SQL is generated from its current (live) definition, not from a version.

The statement cards

Cards appear in this order. Cards marked “when needed” appear only if the table has that content.

Card Notes
Enum Type Definitions When needed. PostgreSQL native enum types used by the table.
CREATE TABLE Statement Columns, keys, defaults.
Check Constraints When needed. Constraints for enum columns that use the CHECK strategy.
CREATE INDEX Statement(s) When the table has indexes.
DROP INDEX Statement(s) When the table has indexes.
DROP TABLE Statement
SELECT Statement All columns, filtered on the primary key (or the first column) with a parameter.
INSERT Statement A parameterized insert of every non-identity column.
UPDATE Statement A parameterized update keyed on the primary key (or the first column if there is none).
DELETE Statement A parameterized delete keyed on the primary key.

The SELECT, INSERT, UPDATE, and DELETE cards are boilerplate. Use them as a starting point.

Spark Schema

The last entry in the dialect dropdown is Spark Schema. It replaces the cards with one Spark Schema card: a PySpark StructType definition of the table, ready to paste into a notebook. The Use IF (NOT) EXISTS checkbox is hidden for this option.

Get SQL for a view, procedure, or function

Views, procedures, and functions don’t have generated SQL. Their SQL is what you (or a Pipeline) wrote, stored once per dialect.

  1. Open the object and click its SQL tab.
  2. Pick the dialect tab you want. The primary dialect has a star.
  3. Click Copy.

To add the object in another dialect, click Add Dialect and pick one. Kenseme uses AI to convert the primary script into the new dialect. If the conversion fails, you get a template to fill in instead. Check converted SQL before you rely on it. See Work with views, procedures, and functions.

Limits of single-object SQL

  • It doesn’t follow dependencies. If you copy the CREATE TABLE for Order and Customer doesn’t exist in the target yet, the foreign key fails.
  • It doesn’t order anything. You decide whether to run a DROP before a CREATE.
  • It doesn’t record a version in z_blaze_version.

To deploy a whole schema in the right order, generate a script from a version instead. See Generate a script from a version.