?
Solved

ERD Diagrams -- Free options & Questions

Posted on 2006-05-18
6
Medium Priority
?
666 Views
Last Modified: 2008-02-01
Hi,
I just took on a client with two databases. Both databases are hosted on SQL Server 2005 and both are currently accessed using MS Access 2003. The databases are about 10 years old and have passed through nearly as many developers so there's a lot of old and unused information and fields. I need to create an ERD for each database and clean out/delete the old fields and tables.

Suggestions I need:
1) Are there any FREE programs that will create ERD's for me? If not, are there any that don't cost hundreds of dollars? The databases are extremely large so don't need an extremely powerful tool.

2) The db's use Access forms and reports for data entry. This is probably a newbie question...but how are forms, reports and  their associated queries represented in an ERD? For the programs that create automatic ERD's WITH RELATIONSHIPS (such as Visio), will they also show what queries, forms and reports are pulling information from the tables? My issue is that I think probably 50%-80% of the forms and reports are old and I want to delete them but I'd like to have an automated way to see all the relationships between forms/queries/reports are so that I can weed out the old stuff.

3) How would you, the expert,  go about the task of cleaning out a medium sized database? What tools would you use?

Any help would be appreciated.

regards,
the goldy
0
Comment
Question by:goldylamont
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:bharatbutani
ID: 16715057
hmm , If the Database is already available in SQL Server, Create a DB Diagram first & see what all are the relationships between the tables.

Next decide what table can be deleted or not ......


Regards
Bharat Butani.
0
 

Author Comment

by:goldylamont
ID: 16715104
i created a DB diagram in SQL Server Managment Studio and no relationships are shown. just all of the tables. am i doing something wrong? keep in mind that Access is used as the front-end so, shouldn't i also need an ERD to show how forms, queries and reports in access are linked to the tables? how can i get relationships to show in SQL Studio and in Access without doing them by hand?

thanks,
goldy
0
 
LVL 3

Assisted Solution

by:bharatbutani
bharatbutani earned 1600 total points
ID: 16715359
hmm it seems like the PK & FK have not been properly designed in your DB , cause then you add the tables in a DB Diagram of SQL Server , & check the add related tables options , it automtically creates the linking among the tables ..........

Can you confirm whether the constraints are present or not ??????


Regards
Bharat Butani.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:goldylamont
ID: 16716694
I can't even select "Add Related Tables" because it is greyed out. Also, the only relationships it will allow me to add if I try or to and from the same table. I can't link one table to another even though they share a PK/FK relationship. aaaaaah...

:-)
0
 
LVL 5

Accepted Solution

by:
nicolasdiogo earned 400 total points
ID: 16717773
i have been in a similar situation and you have to remember taht some objects will reference your tables and these will not be collated within the dependencies infroamtion available in mssql which even worse if no relationships were reinforced with FK & PK.

so you may need to search the syscomments table to search for each table name.  deoending on how many you have, it will take time.

0
 

Author Comment

by:goldylamont
ID: 16858443
thanks guys. sorry it took me so long to get back. you were correct bharatbutani, it's just that no one had even created the relationships on my database yet. I just thought that I didn't know how to show them, but in actuality, they simply weren't there to begin with.

for anyone else reading this, this program is pretty bad-ass for ERD's and it's FREE!!
http://fabforce.net/dbdesigner4/

thanks guys,
goldy
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question