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