# DDL operations

> Every create, alter, copy or drop you can run from the tree starts as generated SQL in a new tab, filled in from the entity's real schema and never executed until you press Run.

Source: https://docs.querylab.io/schema-browser/ddl-operations/

- Table: dim_fuel_type
    - : Search templates…

    <div class="note">
      Use arrow keys to navigate, Enter to select, Escape to close
    </div>

SQL Templates on a table. Categories collapse; the search box filters across all of them.

## Using a template

Right-click a table, dataset or project and choose **SQL Templates**. Pick a template with the mouse or arrow keys; `Enter` opens the generated statement in a new query tab named after it. Nothing runs until you press **Run Query** — read it, edit the parts marked as placeholders, then run.

Templates are filled in from the table's real schema, partitioning and clustering.

## Table templates

| Category      | Template                      | Generates                                                                           |
| ------------- | ----------------------------- | ----------------------------------------------------------------------------------- |
| Query         | SELECT \*                     | `SELECT * FROM …`                                                                   |
| Query         | SELECT all columns            | Every column listed explicitly, nested fields included                              |
| Query         | SELECT all columns (unnested) | The same, with `UNNEST` joins for ARRAY columns                                     |
| DDL           | CREATE TABLE                  | Full schema with modes, descriptions, partitioning, clustering and options          |
| DDL           | INSERT INTO                   | `INSERT INTO … (columns) SELECT …` with the column list filled in                   |
| DDL           | COPY TABLE                    | `CREATE TABLE … COPY …` (duplicates data)                                           |
| DDL           | CLONE TABLE                   | `CREATE TABLE … CLONE …` (metadata only)                                            |
| DDL           | TRUNCATE TABLE                | `TRUNCATE TABLE …`                                                                  |
| DDL           | ALTER TABLE OPTIONS           | `ALTER TABLE … SET OPTIONS (…)` with the current description, labels and expiration |
| DDL           | RENAME TABLE                  | `ALTER TABLE … RENAME TO …`                                                         |
| DDL           | DROP TABLE                    | `DROP TABLE IF EXISTS …` (or `DROP VIEW` for a view)                                |
| Share         | Share Table                   | `GRANT` on the table to a principal                                                 |
| Data Transfer | Export to GCS                 | `EXPORT DATA OPTIONS (uri = 'gs://…')`                                              |
| Data Transfer | Load from GCS                 | `LOAD DATA INTO … FROM FILES (…)`                                                   |

### COPY vs CLONE

```sql
-- COPY: writes every byte again. Billed as a query, takes as long as the table is big.
CREATE TABLE `ace-analytics.warehouse.orders_copy`
COPY `ace-analytics.warehouse.orders`;

-- CLONE: copy-on-write. Free and instant; you pay only for rows that later diverge.
CREATE TABLE `ace-analytics.warehouse.orders_clone`
CLONE `ace-analytics.warehouse.orders`;
```

Clone for backups, staging and experiments. Copy when the result must be independent of the source.

A clone can also be taken at a point in the past with `FOR SYSTEM_TIME AS OF`, and given an expiry so it cleans itself up:

```sql
CREATE TABLE `ace-analytics.warehouse.orders_backup_20260828`
CLONE `ace-analytics.warehouse.orders`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 30 DAY))
```

### ALTER TABLE OPTIONS

The template pre-fills the table's current values so you change one thing and leave the rest intact:

```sql
ALTER TABLE `ace-analytics.warehouse.orders`
SET OPTIONS (
  description = 'Orders fact table, one row per line item',
  labels = [('domain', 'sales'), ('layer', 'fact')],
  expiration_timestamp = NULL,
  require_partition_filter = TRUE
)
```

## Dataset templates

Right-click a dataset → **SQL Templates**. The **Create** category holds a commented skeleton, with the dataset filled in and the options BigQuery accepts, for each object type you can create inside it:

- Table, View
- Procedure
- SQL, JavaScript, Python and Remote Function
- Aggregate SQL and Aggregate JS Function
- Table Function

The rest mirror the table list at dataset level: **CREATE SCHEMA** and **DROP SCHEMA** (DDL), **ALTER SCHEMA OPTIONS** and **ALTER SCHEMA COLLATE** (Alter), **Share Dataset**, **Export to GCS** and **Load from GCS**. An **Information Schema** category lists the dataset's `INFORMATION_SCHEMA` views, each as a ready `SELECT`.

> **DROP SCHEMA is generated with CASCADE.** It deletes every table in the dataset. The statement opens in a tab like any   other. Read it before you run it.

## Project templates

Right-click a project → **SQL Templates** for a **CREATE SCHEMA** template and the **Information Schema Recipes**: ten cost and hygiene queries over `region-…INFORMATION_SCHEMA`, written for the project's region:

| Recipe                          | Answers                                                                   |
| ------------------------------- | ------------------------------------------------------------------------- |
| Unused Tables                   | Which tables no query has touched in N days, and what their storage costs |
| Top Expensive Queries           | The most expensive queries by bytes billed, with on-demand cost           |
| Slots vs On-Demand Cost         | What your workload would cost on each edition versus on-demand            |
| Storage Analysis                | Large and stale tables with time travel, fail-safe and cost breakdown     |
| Cost by User                    | Query cost grouped by user email                                          |
| Slot Utilization Timeline       | Allocated vs used capacity from the reservation timeline                  |
| Failed Queries                  | Recent failures with error text                                           |
| Query Source Distribution       | Which tools run queries: dbt, Looker, Dataform, ad hoc                    |
| Top Expensive Tables            | Tables ranked by query plus storage cost                                  |
| Partitioning & Clustering Audit | Tables that are scanned a lot and could be partitioned or clustered       |

**CREATE Dataset** in the same menu skips the picker and copies a `CREATE SCHEMA` statement to the clipboard.

## Many tables at once

To copy, clone, move, drop or truncate every table matching a name pattern in one script, right-click a project, dataset or table and choose **Bulk table actions…**.
See [Bulk operations](https://docs.querylab.io/data-management/bulk-operations/).

## What permissions you need

Generated DDL runs as you. Creating needs `bigquery.tables.create` on the dataset, altering `bigquery.tables.update`, dropping `bigquery.tables.delete`; `GRANT` statements need `bigquery.datasets.update` (or the table-level equivalent). A missing permission surfaces as a dry-run error in the status bar before anything runs.