fbpx

How to Export the Schema of a SQL Server / MySQL / Postgres / Oracle Database to a CSV File

By Bobby Gill on October 13, 2021

Exporting data from a relational database like SQL Server or Postgres is a relatively straightforward process, as any database management tool worthy of its own title has some sort of “save results to CSV” type of option. But what if what the data you want to export is not the data in the database, but rather the table structure of that database? Below, I’ve put together and shared some very basic queries you can run on each of the major RDBMS systems out there to output the schema of a database, including column names, data types, and foreign key constraints in a tabular format that can be easily saved to a CSV.

SQL Server

SELECT t.TABLE_CATALOG,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,n.CONSTRAINT_TYPE,k2.TABLE_SCHEMA,k2.TABLE_NAME,k2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_CATALOG=c.TABLE_CATALOG
        AND t.TABLE_SCHEMA=c.TABLE_SCHEMA
        AND t.TABLE_NAME=c.TABLE_NAME LEFT JOIN(INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n
    ON k.CONSTRAINT_CATALOG=n.CONSTRAINT_CATALOG
        AND k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA
        AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
    ON k.CONSTRAINT_CATALOG=r.CONSTRAINT_CATALOG
        AND k.CONSTRAINT_SCHEMA=r.CONSTRAINT_SCHEMA
        AND k.CONSTRAINT_NAME=r.CONSTRAINT_NAME)ON c.TABLE_CATALOG=k.TABLE_CATALOG
        AND c.TABLE_SCHEMA=k.TABLE_SCHEMA
        AND c.TABLE_NAME=k.TABLE_NAME
        AND c.COLUMN_NAME=k.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k2
    ON k.ORDINAL_POSITION=k2.ORDINAL_POSITION
        AND r.UNIQUE_CONSTRAINT_CATALOG=k2.CONSTRAINT_CATALOG
        AND r.UNIQUE_CONSTRAINT_SCHEMA=k2.CONSTRAINT_SCHEMA
        AND r.UNIQUE_CONSTRAINT_NAME=k2.CONSTRAINT_NAME
WHERE t.TABLE_TYPE='BASE TABLE';

Postgres

SET enable_nestloop=0;SELECT t.table_catalog,t.table_schema,t.table_name,c.column_name,c.ordinal_position,c.data_type,c.character_maximum_length,n.constraint_type,k2.table_schema,k2.table_name,k2.column_name
FROM information_schema.tables t NATURAL
LEFT JOIN information_schema.columns c LEFT JOIN(information_schema.key_column_usage k NATURAL
JOIN information_schema.table_constraints n NATURAL
LEFT JOIN information_schema.referential_constraints r)ON c.table_catalog=k.table_catalog
        AND c.table_schema=k.table_schema
        AND c.table_name=k.table_name
        AND c.column_name=k.column_name
LEFT JOIN information_schema.key_column_usage k2
    ON k.position_in_unique_constraint=k2.ordinal_position
        AND r.unique_constraint_catalog=k2.constraint_catalog
        AND r.unique_constraint_schema=k2.constraint_schema
        AND r.unique_constraint_name=k2.constraint_name
WHERE t.TABLE_TYPE='BASE TABLE'
        AND t.table_schema NOT IN('information_schema','pg_catalog');

MySQL

SELECT t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,n.CONSTRAINT_TYPE,k.REFERENCED_TABLE_SCHEMA,k.REFERENCED_TABLE_NAME,k.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
        AND t.TABLE_NAME=c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
    ON c.TABLE_SCHEMA=k.TABLE_SCHEMA
        AND c.TABLE_NAME=k.TABLE_NAME
        AND c.COLUMN_NAME=k.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n
    ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA
        AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME
        AND k.TABLE_SCHEMA=n.TABLE_SCHEMA
        AND k.TABLE_NAME=n.TABLE_NAME
WHERE t.TABLE_TYPE='BASE TABLE'
        AND t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql','performance_schema');

Oracle

SELECT ORA_DATABASE_NAME,t.OWNER,t.TABLE_NAME,c.COLUMN_NAME,c.COLUMN_ID,c.DATA_TYPE,c.DATA_LENGTH,n.CONSTRAINT_TYPE,r.OWNER,r.TABLE_NAME,r.COLUMN_NAME
FROM ALL_TABLES t
LEFT JOIN ALL_TAB_COLS c
    ON t.OWNER=c.OWNER
        AND t.TABLE_NAME=c.TABLE_NAME
LEFT JOIN ALL_CONS_COLUMNS nc
    ON c.OWNER=nc.OWNER
        AND c.TABLE_NAME=nc.TABLE_NAME
        AND c.COLUMN_NAME=nc.COLUMN_NAME
LEFT JOIN ALL_CONSTRAINTS n
    ON nc.OWNER=n.OWNER
        AND nc.CONSTRAINT_NAME=n.CONSTRAINT_NAME
        AND n.CONSTRAINT_TYPE IN('P','U','R')LEFT
JOIN ALL_CONS_COLUMNS r
    ON n.R_OWNER=r.OWNER
        AND n.R_CONSTRAINT_NAME=r.CONSTRAINT_NAME
        AND nc.POSITION=r.POSITION
WHERE c.COLUMN_NAME IS NOT NULL;

With any of the above queries you can then simply copy the output in tabular format and paste it into Excel. After that it is a matter of clicking “Save As” and selecting “CSV” as the file format to have the table schema saved into a CSV file.