Are there any good tools for visualising a pre-existing database schema? I'm using MySQL if it matters.
I'm currently using MySQL Workbench to process an SQL create script dump, but it's clunky, slow and a manual process to drag all the tables about (which would be okay if it wasn't so slow).
This question is related to
mysql
database
database-design
schema
visualization
The answer is
I tried DBSchema. Nice features, but wildly slow for a database with about 75 tables. Unusable.
I usually use SchemaSpy to do this, but recently I found a really simple article on sqlfairy that just uses the dump file to create the structure graph
DeZign for Databases might be interesting for you. You can reverse engineer and modify existing databases. Has got an auto-layout function and diagram layout is not meshed up when synchronizing your data model with the database.
SchemaBall is doing visualisation its own interesting way.
When you say visualize the database schema, are you referring to a text-document of all your tables, sprocs, etc., or are you referring to a visual representation of your schema and dependencies?
I know you mentioned MySQL (and it is very possible that it is supported now, but I am not sure), but RedGate has a number of tools that make that pretty simple. It's SQL Doc program can take your entire database and create full-blown database documentation for it. SQL Dependency Tracker can give you a visual representation of all your tables and show their linkings, or you can load in just certain tables or sprocs to show what is using them or what they are using themselves.
I would recommend Toad data modeller
on Mac OS X you can use Sequel Pro
A different approach, but if you're using Ruby on Rails try RailRoad: http://railroad.rubyforge.org
When you say visualize the database schema, are you referring to a text-document of all your tables, sprocs, etc., or are you referring to a visual representation of your schema and dependencies?
I know you mentioned MySQL (and it is very possible that it is supported now, but I am not sure), but RedGate has a number of tools that make that pretty simple. It's SQL Doc program can take your entire database and create full-blown database documentation for it. SQL Dependency Tracker can give you a visual representation of all your tables and show their linkings, or you can load in just certain tables or sprocs to show what is using them or what they are using themselves.
I'm start to create own Perl script based on SQL::Translator module (GraphViz). Here are first results.
Years ago, I used to use Data Architect. I don't know if it's still out there.
You could reverse engineer an existing schema into a relational table diagram.
Or you could go even further, and reverse engineer an Entity-Relationship model with an accompanying diagram. ER diagrams were really useful to me when discussing the data with people who were neither programmers nor database experts.
Sometimes a few manual fixups to the ER model and ER diagram were necessary before it was a useful communication tool with stakeholders.
I like this tool, called simply DbSchema. It's written in Java so it runs on OS X, Windows, or Linux. It's a little clunky, especially when it comes to printing, but from my experience they're all like that. This one is the best of the several I've tried. It makes nice, clear diagrams. Free trial. Costs about $120 depending on how many licenses you buy.
ER/Studio by Embarcadero is one of the costlier ones, but the hierarchical mode it present is by far the best one for understanding database models. It makes query writing the easiest task in the world.
It also is incredible with normalization, denormalization, warehousing, documentation, etc.
The downside is that it is a pretty expensive tool especially when you go multiplatform.
Have you tried the arrange > auto arrange function in MySQL Workbench. It may save you from manually moving the tables around.
How about the SQuirreL SQL Client? As mentioned in another SO question, this programs has the capability to generate a simple ER diagram.
I use SQL::Translator with DBIx::Class
I have DBIx::Class in my toolchain anyway, and combining it with SQL::Translator allows me to convert the schema into lots of different formats. Mostly initialization SQL scripts for different database servers (making it easy to develop on SQLite and move to Postgresql for production) but it can output GraphViz data too, so diagrams are trivial to generate.
Visio professional has a database reverse-engineering tool built into it. You should be able to use it with MySQL through an ODBC driver. It works best when you reverse engineer the database and then create the diagrams by dragging them off the tables and views panel. It will drag any foreign key objects and put them on the diagram as well.
Years ago, I used to use Data Architect. I don't know if it's still out there.
You could reverse engineer an existing schema into a relational table diagram.
Or you could go even further, and reverse engineer an Entity-Relationship model with an accompanying diagram. ER diagrams were really useful to me when discussing the data with people who were neither programmers nor database experts.
Sometimes a few manual fixups to the ER model and ER diagram were necessary before it was a useful communication tool with stakeholders.
I looked for a long time for a decent, and preferably free, tool for linux and found this java application that is quite good (finally!!):
http://sqldeveloper.solyp.com/
Being Java it is cross-platform (I run it on Linux with no issues) and it will connect to any database you can get a JDBC driver for. ie: pretty much any database.
It is quite easy to import your database and get a visual (ERM) of the database schema. The auto-layout feature is good as well, but note that it is not done automatically and you need to click the "automatic layout" button after importing your objects into the diagram.
The application is also a pretty good generic database administration/browsing tool. As one small example, I use it instead of pgadmin for some base development work because of simple niceties like the column width of SQL query results automatically sizing to fit content (which drives me crazy in pgadmin).
How about the SQuirreL SQL Client? As mentioned in another SO question, this programs has the capability to generate a simple ER diagram.
on Mac OS X you can use Sequel Pro
Adminer (formerly phpMinAdmin), the web application for managing MySQL databases, draws simple diagram.
The software itself is similiar to phpMyAdmin, but has more features, its lightweight and it comes in single PHP file.
How about the SQuirreL SQL Client? As mentioned in another SO question, this programs has the capability to generate a simple ER diagram.
Visio professional has a database reverse-engineering tool built into it. You should be able to use it with MySQL through an ODBC driver. It works best when you reverse engineer the database and then create the diagrams by dragging them off the tables and views panel. It will drag any foreign key objects and put them on the diagram as well.
When you say visualize the database schema, are you referring to a text-document of all your tables, sprocs, etc., or are you referring to a visual representation of your schema and dependencies?
I know you mentioned MySQL (and it is very possible that it is supported now, but I am not sure), but RedGate has a number of tools that make that pretty simple. It's SQL Doc program can take your entire database and create full-blown database documentation for it. SQL Dependency Tracker can give you a visual representation of all your tables and show their linkings, or you can load in just certain tables or sprocs to show what is using them or what they are using themselves.
How about the SQuirreL SQL Client? As mentioned in another SO question, this programs has the capability to generate a simple ER diagram.
I would recommend Toad data modeller
Years ago, I used to use Data Architect. I don't know if it's still out there.
You could reverse engineer an existing schema into a relational table diagram.
Or you could go even further, and reverse engineer an Entity-Relationship model with an accompanying diagram. ER diagrams were really useful to me when discussing the data with people who were neither programmers nor database experts.
Sometimes a few manual fixups to the ER model and ER diagram were necessary before it was a useful communication tool with stakeholders.
When you say visualize the database schema, are you referring to a text-document of all your tables, sprocs, etc., or are you referring to a visual representation of your schema and dependencies?
I know you mentioned MySQL (and it is very possible that it is supported now, but I am not sure), but RedGate has a number of tools that make that pretty simple. It's SQL Doc program can take your entire database and create full-blown database documentation for it. SQL Dependency Tracker can give you a visual representation of all your tables and show their linkings, or you can load in just certain tables or sprocs to show what is using them or what they are using themselves.
Adminer (formerly phpMinAdmin), the web application for managing MySQL databases, draws simple diagram.
The software itself is similiar to phpMyAdmin, but has more features, its lightweight and it comes in single PHP file.
I looked for a long time for a decent, and preferably free, tool for linux and found this java application that is quite good (finally!!):
http://sqldeveloper.solyp.com/
Being Java it is cross-platform (I run it on Linux with no issues) and it will connect to any database you can get a JDBC driver for. ie: pretty much any database.
It is quite easy to import your database and get a visual (ERM) of the database schema. The auto-layout feature is good as well, but note that it is not done automatically and you need to click the "automatic layout" button after importing your objects into the diagram.
The application is also a pretty good generic database administration/browsing tool. As one small example, I use it instead of pgadmin for some base development work because of simple niceties like the column width of SQL query results automatically sizing to fit content (which drives me crazy in pgadmin).
I tried DBSchema. Nice features, but wildly slow for a database with about 75 tables. Unusable.
Have you tried the arrange > auto arrange function in MySQL Workbench. It may save you from manually moving the tables around.
SchemaBall is doing visualisation its own interesting way.
I'm start to create own Perl script based on SQL::Translator module (GraphViz). Here are first results.
Years ago, I used to use Data Architect. I don't know if it's still out there.
You could reverse engineer an existing schema into a relational table diagram.
Or you could go even further, and reverse engineer an Entity-Relationship model with an accompanying diagram. ER diagrams were really useful to me when discussing the data with people who were neither programmers nor database experts.
Sometimes a few manual fixups to the ER model and ER diagram were necessary before it was a useful communication tool with stakeholders.
Have you tried the arrange > auto arrange function in MySQL Workbench. It may save you from manually moving the tables around.
I usually use SchemaSpy to do this, but recently I found a really simple article on sqlfairy that just uses the dump file to create the structure graph
I use SQL::Translator with DBIx::Class
I have DBIx::Class in my toolchain anyway, and combining it with SQL::Translator allows me to convert the schema into lots of different formats. Mostly initialization SQL scripts for different database servers (making it easy to develop on SQLite and move to Postgresql for production) but it can output GraphViz data too, so diagrams are trivial to generate.
A different approach, but if you're using Ruby on Rails try RailRoad: http://railroad.rubyforge.org
Try PHPMyAdmin which has some really nice visualisation and editing feature. I am pretty sure you can even export to exel from it.
I like this tool, called simply DbSchema. It's written in Java so it runs on OS X, Windows, or Linux. It's a little clunky, especially when it comes to printing, but from my experience they're all like that. This one is the best of the several I've tried. It makes nice, clear diagrams. Free trial. Costs about $120 depending on how many licenses you buy.
DeZign for Databases might be interesting for you. You can reverse engineer and modify existing databases. Has got an auto-layout function and diagram layout is not meshed up when synchronizing your data model with the database.
ER/Studio by Embarcadero is one of the costlier ones, but the hierarchical mode it present is by far the best one for understanding database models. It makes query writing the easiest task in the world.
It also is incredible with normalization, denormalization, warehousing, documentation, etc.
The downside is that it is a pretty expensive tool especially when you go multiplatform.
I would recommend Toad data modeller
Try PHPMyAdmin which has some really nice visualisation and editing feature. I am pretty sure you can even export to exel from it.
Source: Stackoverflow.com