Retrieve SQL database schemas as an array

SQL queries for retrieving your database schema.

Table of contents

Introduction

It is recommended to include your database schema when using the AI-powered SQL generator (text-to-SQL) and the best format to retrieve the database schema in is as an array. Including the database schema ensures not only that the SQL generations are much more accurate, but also that you can include much larger database schemas without confusing AI. To get the database schema as an array simply run the query and include the outputted array as dataSource. Below you get get the query for your database engine.

PostgreSQL

SELECT
  json_agg(
    json_build_object(
      'table',
      cols.table_name,
      'column',
      cols.column_name,
      'type',
      cols.data_type,
      'references',
      CASE
        WHEN ccu.table_name IS NOT NULL THEN ccu.table_name || '.' || ccu.column_name
        ELSE NULL
      END
    )
    ORDER BY
      cols.table_name
  ) AS schema_json
FROM
  information_schema.columns AS cols
  LEFT JOIN information_schema.key_column_usage AS kcu ON kcu.table_schema = cols.table_schema
  AND kcu.table_name = cols.table_name
  AND kcu.column_name = cols.column_name
  LEFT JOIN information_schema.table_constraints AS tc ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
  AND tc.constraint_type = 'FOREIGN KEY'
  LEFT JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  AND ccu.constraint_schema = tc.constraint_schema
WHERE
  cols.table_schema = current_schema();

MySQL

SELECT
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'table',
      cols.TABLE_NAME,
      'column',
      cols.COLUMN_NAME,
      'type',
      cols.DATA_TYPE,
      'references',
      CASE
        WHEN kcu.REFERENCED_TABLE_NAME IS NOT NULL THEN CONCAT(
          kcu.REFERENCED_TABLE_NAME,
          '.',
          kcu.REFERENCED_COLUMN_NAME
        )
        ELSE NULL
      END
    )
  ) AS schema_json
FROM
  information_schema.COLUMNS AS cols
  JOIN information_schema.TABLES AS tbl ON tbl.TABLE_SCHEMA = cols.TABLE_SCHEMA
  AND tbl.TABLE_NAME = cols.TABLE_NAME
  LEFT JOIN information_schema.KEY_COLUMN_USAGE AS kcu ON kcu.TABLE_SCHEMA = cols.TABLE_SCHEMA
  AND kcu.TABLE_NAME = cols.TABLE_NAME
  AND kcu.COLUMN_NAME = cols.COLUMN_NAME
WHERE
  cols.TABLE_SCHEMA = DATABASE()
  AND tbl.TABLE_TYPE = 'BASE TABLE';

Microsoft SQL Server

SELECT
(
  SELECT
    cols.TABLE_NAME AS [table],
    cols.COLUMN_NAME AS [column],
    cols.DATA_TYPE AS [type],
    fk.relation AS [references]
  FROM INFORMATION_SCHEMA.COLUMNS AS cols
  OUTER APPLY (
    SELECT TOP 1
      rt.name + '.' + rc.name AS relation
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.tables AS rt
        ON fkc.referenced_object_id = rt.object_id
    INNER JOIN sys.columns AS rc
        ON fkc.referenced_object_id = rc.object_id
        AND fkc.referenced_column_id = rc.column_id
    WHERE fkc.parent_object_id = OBJECT_ID(QUOTENAME(cols.TABLE_SCHEMA) + '.' + QUOTENAME(cols.TABLE_NAME))
      AND COL_NAME(fkc.parent_object_id, fkc.parent_column_id) = cols.COLUMN_NAME
  ) AS fk
  WHERE cols.TABLE_CATALOG = DB_NAME()
  FOR JSON PATH
) AS schema_json;

Oracle PL/SQL

SELECT
  json_agg(
    json_build_object(
      'table',
      cols.table_name,
      'column',
      cols.column_name,
      'type',
      cols.data_type,
      'references',
      CASE
        WHEN ccu.table_name IS NOT NULL THEN ccu.table_name || '.' || ccu.column_name
        ELSE NULL
      END
    )
  ) AS schema_json
FROM
  information_schema.columns AS cols
  LEFT JOIN information_schema.key_column_usage AS kcu ON kcu.table_schema = cols.table_schema
  AND kcu.table_name = cols.table_name
  AND kcu.column_name = cols.column_name
  LEFT JOIN information_schema.table_constraints AS tc ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
  AND tc.constraint_type = 'FOREIGN KEY'
  LEFT JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  AND ccu.constraint_schema = tc.constraint_schema
WHERE
  cols.table_schema = current_schema();

SQLite

SELECT
  json_group_array(
    json_object(
      'table',
      table_name,
      'column',
      column_name,
      'type',
      column_type,
      'references',
      (
        SELECT
          fk."table" || '.' || fk."to"
        FROM
          pragma_foreign_key_list (table_name) AS fk
        WHERE
          fk."from" = column_name
        LIMIT
          1
      )
    )
  ) AS schema_json
FROM
  (
    SELECT
      m.name AS table_name,
      ti.name AS column_name,
      ti.type AS column_type
    FROM
      sqlite_master m,
      pragma_table_info (m.name) ti
    WHERE
      m.type = 'table'
  ) AS columns;

BigQuery

Update your_dataset with your dataset name.

SELECT
  JSON_AGG (
    JSON_OBJECT (
      'table',
      cols.table_name,
      'column',
      cols.column_name,
      'type',
      cols.data_type,
      'references',
      IF(
        kcu.referenced_table_name IS NOT NULL,
        CONCAT(
          kcu.referenced_table_name,
          '.',
          kcu.referenced_column_name
        ),
        NULL
      )
    )
  ) AS schema_json
FROM
  `your_dataset.INFORMATION_SCHEMA.COLUMNS` AS cols -- REPLACE your_dataset
  LEFT JOIN `your_dataset.INFORMATION_SCHEMA.KEY_COLUMN_USAGE` AS kcu -- REPLACE your_dataset
  ON cols.table_name = kcu.table_name
  AND cols.column_name = kcu.column_name;

Snowflake

SELECT
  ARRAY_AGG(
    OBJECT_CONSTRUCT(
      'table',
      cols.table_name,
      'column',
      cols.column_name,
      'type',
      cols.data_type,
      'references',
      CASE
        WHEN ccu.table_name IS NOT NULL THEN ccu.table_name || '.' || ccu.column_name
        ELSE NULL
      END
    )
  ) AS schema_json
FROM
  information_schema.columns AS cols
  LEFT JOIN information_schema.key_column_usage AS kcu ON kcu.table_schema = cols.table_schema
  AND kcu.table_name = cols.table_name
  AND kcu.column_name = cols.column_name
  LEFT JOIN information_schema.table_constraints AS tc ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
  AND tc.constraint_type = 'FOREIGN KEY'
  LEFT JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  AND ccu.constraint_schema = tc.constraint_schema
WHERE
  cols.table_schema = CURRENT_SCHEMA();

MongoDB

{"listCollections": 1, "nameOnly": true}