November 23, 2010 at 4:03 am
Hi,
I need to work on an existing database which has no documentation , E-R diagrams and need to document the entire design and build class diagram.
I need your help on how to start the process.
What i think is to retreive most accessed tables from the DB, script the dependencies, analyze them and build E-R diagrams.
Can you people provide me few suggestions. What i expect is, if any tool is available for buliding E-R diagrams based on the dependencies.
Thanks,
Naren.
November 23, 2010 at 5:04 am
First thing I'd do is get the data model. Do you have a data modelling tool like ERStudio or ER/Win? If not, you'll have to use the built in model in SQL Server. That will show you what you have in the way of tables and which of them are missing primary keys or foreign keys, that sort of thing.
With that information in hand you can then start examining access to the system. Assuming it's a 2005 or above system, I'd start with checking out the index usage stats in sys.dm_db_index_usage_stats. You can also see an aggregation of queries in the cache by looking at sys.dm_exec_query_stats. That should allow you to identify the most frequently accessed queries & indexes. After that it's just lots of work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2010 at 12:46 pm
Narendra-274001 (11/23/2010)
I need to work on an existing database which has no documentation , E-R diagrams and need to document the entire design and build class diagram.I need your help on how to start the process.
What i think is to retreive most accessed tables from the DB, script the dependencies, analyze them and build E-R diagrams.
Can you people provide me few suggestions. What i expect is, if any tool is available for buliding E-R diagrams based on the dependencies.
This looks like a job for ERWin - or any other tool alike.
ERWin allows you to do reverse engineering meaning, point to an existing database and get exactly what you are looking for.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 7, 2010 at 12:29 am
Thanks for your suggestions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply