Skip to main content

Generate an ERD Diagram from Data

  • Updated

 

SmartDraw can generate 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 using the ERD Diagram Data Visualizer.

 

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, click the + icon to switch the SmartPanel into the Insert Content view.

Once on the Insert Content view, select the Add Data Visualizer category and click on ERD Diagram.

Drag and drop your table definition CSV file to the modal or click choose files to browse through your system.

Once the file is loaded and parsed, you can Show Table List to see how SmartDraw sees your data.

You can check or uncheck any data tables you want to include in your final ERD.

Click Import when you're ready to generate your diagram.

Once the import is complete, you can edit your database diagram like any other SmartDraw diagram. 

erd-generated.png