ERD (Database) Diagram Extension

Getting Started with ERD Extensions in Under 5 Minutes

Watch this short video tutorial to learn how to create an ERD Diagram in SmartDraw using Extensions.

The SmartDraw ERD Extension generates a visual representation of your database automatically. First, you'll want to export a CSV of your table definitions from your database server. SmartDraw will use this file to generate a visual of your database.

Preparing Your 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

Build Your ERD Diagram

Once you have a CSV file from your database, you're ready to generate your ERD. To get started, select Automatic ERD (Database) Diagram in the Extensions category.

In the modal, click "Browse" and find your table definition CSV file.

Next, you'll want to select the columns and types you want to visualize.

When you're ready to create your ER diagram, click "Build Diagram". Once you've opened your document you can edit your database diagram like any other SmartDraw diagram.