SQL Queries to Export Database Schema for SQL Server, MySQL, PostgreSQL, and Oracle

how-to

Table of Contents
  1. SQL Server
  2. MySQL
  3. PostgreSQL
  4. Oracle
  5. Importing it into AI Bot
  6. References

Exporting database schema is depends on the database type you are using. The queries export the following information (sample output is shown below):

Schema Table Column Type Length Nullable Default
public User id text Null No Null

The following queries show how to export database schema for the most popular databases (if you spot any mistake in the queries, please let us know):

SQL Server

SELECT
  TABLE_SCHEMA AS [Schema],
  TABLE_NAME AS [Table],
  COLUMN_NAME AS [Column],
  DATA_TYPE AS [Type],
  CHARACTER_MAXIMUM_LENGTH AS [Length],
  IS_NULLABLE AS [Nullable],
  COLUMN_DEFAULT AS [Default],
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
  -- AND TABLE_CATALOG='YOUR_TABLE_CATALOG' // If you are using table catalog
ORDER BY
  TABLE_SCHEMA,
  TABLE_NAME,
  ORDINAL_POSITION

MySQL

SELECT
  TABLE_SCHEMA AS `Schema`,
  TABLE_NAME AS `Table`,
  COLUMN_NAME AS `Column`,
  DATA_TYPE AS `Type`,
  CHARACTER_MAXIMUM_LENGTH AS `Length`,
  IS_NULLABLE AS `Nullable`,
  COLUMN_DEFAULT AS `Default`,
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_SCHEMA = "YOUR_DATABASE_NAME"
ORDER BY
  TABLE_SCHEMA,
  TABLE_NAME,
  ORDINAL_POSITION

PostgreSQL

SELECT
  TABLE_SCHEMA AS "Schema",
  TABLE_NAME AS "Table",
  COLUMN_NAME AS "Column",
  DATA_TYPE AS "Type",
  CHARACTER_MAXIMUM_LENGTH AS "Length",
  IS_NULLABLE AS "Nullable",
  COLUMN_DEFAULT AS "Default"
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
ORDER BY
  TABLE_SCHEMA,
  TABLE_NAME,
  ORDINAL_POSITION

Oracle

SELECT
  TABLE_SCHEMA AS "Schema",
  TABLE_NAME AS "Table",
  COLUMN_NAME AS "Column",
  DATA_TYPE AS "Type",
  CHARACTER_MAXIMUM_LENGTH AS "Length",
  IS_NULLABLE AS "Nullable",
  COLUMN_DEFAULT AS "Default"
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
ORDER BY
  TABLE_SCHEMA,
  TABLE_NAME,
  ORDINAL_POSITION

Importing it into AI Bot

After exporting your database schema, export it as a CSV file and you can import it into AI Bot (Click the Settings button). After importing it, you can use it as autosuggestion for your AI Bot by typing / (slash) in the query editor.

References