Generate table relationship diagram from existing schema (SQL Server)

Question

Is there a way to produce a diagram showing existing tables and their relationships given a connection to a database?

This is for SQL Server 2008 Express Edition.

a free tool named [SchemaSpy](http://schemaspy.sourceforge.net/) can do that like charm. But, needs a little setup. I have detailed the step here: http://blog.kmonsoor.com/generate-er-diagram-from-sql-database/ — kmonsoor, Dec 01 '15 at 21:48
nice one: 60 DB Reverse engineering tools (see filter on the left) - https://dbmstools.com/categories/database-diagram-tools/sqlserver — George Birbilis, Feb 26 '20 at 09:40

score 162 · Accepted Answer · edited Dec 04 '18 at 07:49

162

Yes you can use SQL Server 2008 itself but you need to install SQL Server Management Studio Express (if not installed ) . Just right Click on Database Diagrams and create new diagram. Select the exisiting tables and if you have specified the references in your tables properly. You will be able to see the complete diagram of selected tables. For further reference see Getting started with SQL Server database diagrams

edited Dec 04 '18 at 07:49

Karel

369
1
5
20

answered Oct 03 '08 at 20:43

Gripsoft

2,570
7
27
30

2

Any way to do this with SQL Server Compact 3.5? – Scott Anderson May 12 '09 at 21:33
One should be able to see all the existing relationships as well once you add all the necessary tables. – Vishwas S L Aug 29 '18 at 07:17

score 56 · Answer 2 · edited Jan 21 '19 at 21:22

56

Try DBVis - download at https://www.dbvis.com/download - there is a pro version (not needed) and a open version that should suffice.

All you have to do is to get the right JDBC - database driver for SQL Server, the tool shows tables and references orthogonal, hierarchical, in a circle ;-) etc. just by pressing one single button. I use the free version for years now.

edited Jan 21 '19 at 21:22

Maytham Fahmi

31,138
14
118
137

answered Oct 03 '08 at 20:30

Georgi

4,402
4
24
20

1

This was very easy to set up, and produced a much more readable diagram than the SQL Server tools do. +1 – davidtbernal Jan 12 '11 at 19:34
Very easy to set up iff you're already running JDBC. Otherwise potentially in [all to familiar config heck](http://stackoverflow.com/questions/1086764/how-can-i-make-ms-sql-server-available-for-connections). – ruffin Jun 16 '14 at 15:42
Works nicely if you have proper foreign key relationships. – Phil Hord Jul 23 '18 at 22:35
In addition to this answer; if you face connection problem check this https://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-address the. The other JDBC info https://www.dbvis.com/features/sqlserver-database-drivers/ and for updated JDBC driver look at this https://www.microsoft.com/en-us/download/details.aspx?id=57175 – Maytham Fahmi Jan 21 '19 at 21:24
When specifying the Database port, I used the Dynamic Port of the sql server instead. The regular port 1433 does not work for me. – Harvey Darvey Feb 26 '19 at 04:10

score 13 · Answer 3 · answered Oct 05 '08 at 01:53

13

SQLDeveloper can do this.

http://sqldeveloper.solyp.com/

answered Oct 05 '08 at 01:53

SquareCog

19,421
8
49
63

This link now redirects to the home page: https://www.solyp.com/. Seems deprecated. – R. Schreurs Nov 12 '19 at 14:23

score 8 · Answer 4 · edited Nov 24 '11 at 15:49

8

For SQL statements you can try reverse snowflakes. You can join at sourceforge or the demo site at http://snowflakejoins.com/.

edited Nov 24 '11 at 15:49

canadian_scholar

1,315
12
26

answered May 07 '11 at 21:10

Pascal

81
1
1

Doesn't do the task (visualizing the database schema) but is still pretty cool though. I wish there were something like this to generate a tidy diagram of a computer network from a textual description... – Ivan Apr 27 '13 at 03:04

score 4 · Answer 5 · answered Oct 03 '08 at 20:24

4

Why don't you just use the database diagram functionality built into SQL Server?

answered Oct 03 '08 at 20:24

Mitchel Sellers

62,228
14
110
173

6

It is not always available.. some versions of SSMS and SQL do not allow of due to licensing. – Piotr Kula Jul 31 '12 at 13:21
Or you can, you know be on GNU/LInux or Macos actually without any license, just with connection credentials. – helvete Feb 25 '20 at 16:12

score 2 · Answer 6 · answered Oct 03 '08 at 20:36

2

Visio Professional has a database reverse-engineering feature if yiu create a database diagram. It's not free but is fairly ubiquitous in most companies and should be fairly easy to get.

Note that Visio 2003 does not play nicely with SQL2005 or SQL2008 for reverse engineering - you will need to get 2007.

answered Oct 03 '08 at 20:36

ConcernedOfTunbridgeWells

64,444
15
143
197

7

This feature has unfortunately been removed in Visio 2013. – Kjell-Åke Gafvelin Apr 04 '13 at 14:21

score 2 · Answer 7 · answered Jan 11 '12 at 11:48

2

DeZign for Databases should be able to do this just fine.

answered Jan 11 '12 at 11:48

Thor Hovden

413
6
13

Sualeh Fatehi · Answer 8 · 2013-04-26T00:21:54.190

1

SchemaCrawler for SQL Server can generate database diagrams, with the help of GraphViz. Foreign key relationships are displayed (and can even be inferred, using naming conventions), and tables and columns can be excluded using regular expressions.

edited Apr 26 '13 at 00:21

answered May 11 '10 at 02:43

Sualeh Fatehi

4,700
2
24
28

1

"Foreign key relationships are inferred, using naming conventions" - totally inapplicable for in the most of real business cases. – Ivan Apr 24 '13 at 20:33
2

You'd be surprised how often this is needed. – Phil Hord Jul 23 '18 at 22:37

score 0 · Answer 9 · answered Mar 23 '13 at 12:55

0

MySQL WorkBench is free software and is developed by Oracle, you can import an SQL File or specify a database and it will generate an SQL Diagram which you can move around to make it more visually appealing. It runs on GNU/Linux and Windows and it's free and has a professional look..

answered Mar 23 '13 at 12:55

dendini

3,842
9
37
74

It doesn't seem to support DBMSes other than MySQL any more. Also, its diagram auto-layout is dreadful. – Ivan Apr 23 '13 at 22:39
the auto layout simply stacks everything on top of another, you must then drag each table to the appropriate position, it might be dreadful but I doubt any algorithm would be smart enough to order everything neatly. As for SQL support I use it with H2 server queries and it works, I think they support most SQL-92 compliant SQL so if your code doesn't comply to SQL-92 it might be a good occasion to check it. – dendini Apr 24 '13 at 09:43
The incompatibilities lie everywhere from very basic things like quotes/brackets usage to huge differences in DDL (tables/keys/constraints/indices definition language) and fundamental differences in types (like using a separate type for Unicode strings). – Ivan Apr 27 '13 at 20:17

Generate table relationship diagram from existing schema (SQL Server)

9 Answers9

Linked