ERD Diagrams -- Free options & Questions

Posted on 2006-05-18
Last Modified: 2008-02-01
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.

the goldy
Question by:goldylamont
    LVL 3

    Expert Comment

    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 ......

    Bharat Butani.

    Author Comment

    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?

    LVL 3

    Assisted Solution

    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 ??????

    Bharat Butani.

    Author Comment

    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...

    LVL 5

    Accepted Solution

    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.


    Author Comment

    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!!

    thanks guys,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    SQL Server Degrading on Write 13 50
    SQL Dynamic Insert 18 40
    Software suggestion 12 17
    Delete from table 6 26
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now