Preparing Your Data to Generate ERD or Database Diagrams

Before SmartDraw can generate your database diagram, you'll have to export your data from your database. Below you'll find some scripts you can use for some common database types to get the data you'll need. Learn more about how to generate an ER diagram automatically using your exported data.

For Microsoft SQL Server

Run this query, modifying for your specific needs.

SELECT DISTINCT
SDTables.TABLE_CATALOG as DatabaseName,
SDTables.TABLE_SCHEMA as ParentSchema,
SDTables.TABLE_NAME as ParentTable,
SDColumns.COLUMN_NAME as ColumnName,
SDColumns.ORDINAL_POSITION as ColumnOrder,
SDColumns.DATA_TYPE as DataType,
SDColumns.CHARACTER_MAXIMUM_LENGTH as ColumnSize,
SDConstraints.CONSTRAINT_TYPE as ConstraintType,
SDKeys.TABLE_SCHEMA as ChildSchema,
SDKeys.TABLE_NAME as ChildTable,
SDKeys.COLUMN_NAME as ChildColumn
FROM INFORMATION_SCHEMA.TABLES SDTables
LEFT JOIN INFORMATION_SCHEMA.COLUMNS SDColumns
ON SDTables.TABLE_CATALOG=SDColumns.TABLE_CATALOG
AND SDTables.TABLE_SCHEMA=SDColumns.TABLE_SCHEMA
AND SDTables.TABLE_NAME=SDColumns.TABLE_NAME
LEFT JOIN
(
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE SDKeys2
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS SDConstraints
    ON SDKeys2.CONSTRAINT_CATALOG=SDConstraints.CONSTRAINT_CATALOG
    AND SDKeys2.CONSTRAINT_SCHEMA=SDConstraints.CONSTRAINT_SCHEMA
    AND SDKeys2.CONSTRAINT_NAME=SDConstraints.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS SDReference
    ON SDKeys2.CONSTRAINT_CATALOG=SDReference.CONSTRAINT_CATALOG
    AND SDKeys2.CONSTRAINT_SCHEMA=SDReference.CONSTRAINT_SCHEMA
    AND SDKeys2.CONSTRAINT_NAME=SDReference.CONSTRAINT_NAME)
ON SDColumns.TABLE_CATALOG=SDKeys2.TABLE_CATALOG
AND SDColumns.TABLE_SCHEMA=SDKeys2.TABLE_SCHEMA
AND SDColumns.TABLE_NAME=SDKeys2.TABLE_NAME
AND SDColumns.COLUMN_NAME=SDKeys2.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE SDKeys
ON SDKeys2.ORDINAL_POSITION=SDKeys.ORDINAL_POSITION
AND SDReference.UNIQUE_CONSTRAINT_CATALOG=SDKeys.CONSTRAINT_CATALOG
AND SDReference.UNIQUE_CONSTRAINT_SCHEMA=SDKeys.CONSTRAINT_SCHEMA
AND SDReference.UNIQUE_CONSTRAINT_NAME=SDKeys.CONSTRAINT_NAME
WHERE SDTables.TABLE_TYPE='BASE TABLE'
ORDER By ParentSchema, ParentTable, ColumnOrder

For MySQL

Run this query, modifying for your specific needs.

SELECT DISTINCT
'' as DatabaseName,
SDTables.TABLE_SCHEMA as ParentSchema,
SDTables.TABLE_NAME as ParentTable,
SDColumns.COLUMN_NAME as ColumnName,
SDColumns.ORDINAL_POSITION as ColumnOrder,
SDColumns.DATA_TYPE as DataType,
SDColumns.CHARACTER_MAXIMUM_LENGTH as ColumnSize,
SDConstraints.CONSTRAINT_TYPE as ConstraintType,
SDKeys.REFERENCED_TABLE_SCHEMA as ChildSchema,
SDKeys.REFERENCED_TABLE_NAME as ChildTable,
SDKeys.REFERENCED_COLUMN_NAME as ChildColumn
FROM
INFORMATION_SCHEMA.TABLES SDTables
LEFT JOIN
INFORMATION_SCHEMA.COLUMNS SDColumns ON SDTables.TABLE_SCHEMA = SDColumns.TABLE_SCHEMA
AND SDTables.TABLE_NAME = SDColumns.TABLE_NAME
LEFT JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE SDKeys ON SDColumns.TABLE_SCHEMA = SDKeys.TABLE_SCHEMA
AND SDColumns.TABLE_NAME = SDKeys.TABLE_NAME
AND SDColumns.COLUMN_NAME = SDKeys.COLUMN_NAME
LEFT JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS SDConstraints ON SDKeys.CONSTRAINT_SCHEMA = SDConstraints.CONSTRAINT_SCHEMA
AND SDKeys.CONSTRAINT_NAME = SDConstraints.CONSTRAINT_NAME
AND SDKeys.TABLE_SCHEMA = SDConstraints.TABLE_SCHEMA
AND SDKeys.TABLE_NAME = SDConstraints.TABLE_NAME
WHERE
SDTables.TABLE_TYPE = 'BASE TABLE'
AND SDTables.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' , 'mysql','performance_schema','sys')
ORDER BY ParentSchema, ParentTable, ColumnOrder

For PostgreSQL

Run this query, modifying for your specific needs.

SELECT DISTINCT
SDTables.table_catalog as DatabaseName,
SDTables.table_schema as ParentSchema,
SDTables.table_name as ParentTable,
SDColumns.column_name as ColumnName,
SDColumns.ordinal_position as ColumnOrder,
SDColumns.data_type as DataType,
SDColumns.character_maximum_length as ColumnSize,
SDConstraints.constraint_type as ConstraintType,
SDKeys2.table_schema as ChildSchema,
SDKeys2.table_name as ChildTable,
SDKeys2.column_name as ChildColumn
FROM information_schema.tables SDTables
NATURAL LEFT JOIN information_schema.columns SDColumns
LEFT JOIN
(information_schema.key_column_usage SDKeys
NATURAL JOIN information_schema.table_constraints SDConstraints
NATURAL LEFT JOIN information_schema.referential_constraints SDReference
)
ON SDColumns.table_catalog=SDKeys.table_catalog AND SDColumns.table_schema=SDKeys.table_schema AND SDColumns.table_name=SDKeys.table_name AND SDColumns.column_name=SDKeys.column_name
LEFT JOIN information_schema.key_column_usage SDKeys2
ON SDKeys.position_in_unique_constraint=SDKeys2.ordinal_position AND SDReference.unique_constraint_catalog=SDKeys2.constraint_catalog AND SDReference.unique_constraint_schema=SDKeys2.constraint_schema AND SDReference.unique_constraint_name=SDKeys2.constraint_name
WHERE SDTables.TABLE_TYPE='BASE TABLE' AND SDTables.table_schema NOT IN('information_schema','pg_catalog')
ORDER BY ParentSchema, ParentTable, ColumnOrder

For Oracle

Run this query, modifying for your specific needs.

SELECT DISTINCT
    ORA_DATABASE_NAME as DatabaseName,
    SDTables.OWNER as ParentSchema,
    SDTables.TABLE_NAME as ParentTable,
    SDColumns.COLUMN_NAME as ColumName,
    SDColumns.COLUMN_ID as ColumnOrder,
    SDColumns.DATA_TYPE as DataType,
    SDColumns.DATA_LENGTH as ColumnSize,
    SDConstraints.CONSTRAINT_TYPE as ConstraintType,
    SDChildColumns.OWNER as ChildSchema,
    SDChildColumns.TABLE_NAME as ChildTable,
    SDChildColumns.COLUMN_NAME as ChildColumn
FROM
    ALL_TABLES SDTables
    LEFT JOIN ALL_TAB_COLS SDColumns
        ON SDTables.OWNER=SDColumns.OWNER
        AND SDTables.TABLE_NAME=SDColumns.TABLE_NAME
    LEFT JOIN ALL_CONS_COLUMNS SDConstraintCol
        ON SDColumns.OWNER=SDConstraintCol.OWNER
        AND SDColumns.TABLE_NAME=SDConstraintCol.TABLE_NAME
        AND SDColumns.COLUMN_NAME=SDConstraintCol.COLUMN_NAME
    LEFT JOIN ALL_CONSTRAINTS SDConstraints
        ON SDConstraintCol.OWNER=SDConstraints.OWNER
        AND SDConstraintCol.CONSTRAINT_NAME=SDConstraints.CONSTRAINT_NAME
        AND SDConstraints.CONSTRAINT_TYPE IN('P','U','R')
    LEFT JOIN ALL_CONS_COLUMNS SDChildColumns
        ON SDConstraints.R_OWNER=SDChildColumns.OWNER
        AND SDConstraints.R_CONSTRAINT_NAME=SDChildColumns.CONSTRAINT_NAME
        AND SDConstraintCol.POSITION=SDChildColumns.POSITION
WHERE
    SDColumns.COLUMN_NAME IS NOT NULL
ORDER BY
    ParentSchema, ParentTable, ColumnOrder
By continuing to use the website, you consent to the use of cookies.   Read More