SQL Queries to Export Database Schema for SQL Server, MySQL, PostgreSQL, and Oracle
how-to
Table of Contents
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.