Viewing Database Schema/Relationships

[jdonalds]

I'm not sure if "Schema" is the right word, but I was hoping to print out a picture that displays all the tables in a database along with their relationship to one another. I know you could do this with Access, but how about SQL Server?

Thanks,
Jeremy

 

[mrdenny]

You can do this with SQL Server. In Enteprise Manager you can create a diagram and print it. But keep in mind that you have no control of how it prints. I would recommend using visio to make the diagram.

Visio can reverse engineer your database and give you a nice printable schema.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jdonalds]

I've attempted to reverse engineer the database using visio 2000, but I'm not sure how to show the relationships. Do I have to manually draw and connect the fields or should it do that for me automatically. Any instruction on how to do this successfully would be much appreciated.

Thanks,
Jeremy

 

[mrdenny]

If the relationships are defined within the database using forign key constraints then the relationships should automatically show up in visio. If they aren't defined within the database, then you will need to draw the constraints manually.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

First you have to make sure that you selected SQL Server as the "driver" (Database>Options>Drivers). Then when you use the Reverse Engineer wizard, it should give you the option to reverse engineer the relationships.

-SQLBill

 

[jdonalds]

SQLBill,

I've attempted to do this but it doesn't mention reverse engineering the relationships. Only tables, views and stored proceedures. Under tables you have the option of Primary keys, indexes, foreign keys, Triggers and Check clauses but not relationships specifically. When I drag all the tables to the screen it does not automatically show the relationships. Any suggestions?

 

[SQLBill]

Before you reverse engineer, Click on Database>Options>Document. There should be a tab that allows you to select what you want reverse engineered. One option is relationships.

-SQLBill

 

[jdonalds]

Did that but whenever I drag the tables to the diagram it doesn't display the relationships, i.e. it doesn't show the lines between tables. Sorry to make this so difficult.

Jeremy

 

[SQLSister]

Are you positive anyone has actually defined the relationships in SQL server?

Questions about posting. See faq183-874

 

[jdonalds]

No I am not, I wasn't associated with the development of this particular database. But it is a production database, wouldn't they have to be defined in order to function properly?

 

[SQLBill]

Nope, a database can be created with hundreds of tables and no relationship between them.

Scenerio....I'm a DBA, call me lazy, call me unknowledgeable, whatever....I decide that I'm not going to let anyone create their own databases unless they require 10 or more tables. Otherwise, everyone will create their tables in MyOnlyDB. Makes it easier for me to administer it. Now 20 users each create one table for themselves. Is there a relationship required between the 20 tables? No.

While that's a REALLY far-off scenerio, it does show you aren't required to have relationships between tables in a database.

(BTW- I would NEVER do anything like that, it was just an example).

-SQLBill