Reverse Engineering a SQL Server 2005 Database
For small databases, I was quite liking Visual Studio for Database Professionals. It is easy to use and fantastic for managing your schema as source.
However it seems to be balking at reverse generating a giant database with 2000+ stored procedures. (It is also generally very slow, demanding validation of every little change to your schema against a running SQL Server instance)
If you reach the limitations of a tool, what are the alternatives? I wanted to keep with Microsoft for licencing issues. (The code produced is used in an enterprise environment)
TableDiff comes free with SQL Server 2005. However it does everything table by table.
This blog showshow to use the ‘Generate Scripts…’ command to produce the SQL source of all the objects. (Even as a single file) It even lets me script all of the object of a certain type (for example, all the views) but doesn’t seem to put the objects back in the right order. Also, sometimes it runs very fast (a few seconds) and other time extremely slow (a few hours).
The conclusion? Visual Studio is still the best way to manage your schema. Just be aware that there are limits to what you can throw at it 
February 14, 2008 at 11:27 pm
A friend of mine recently regenerated his database contents via DB Pro and found that it was fairly quick and efficient with proper warnings of invalid schema references.
I’m still unsure about the limits of such an operation. I’d like to hear more if you can narrow down the causes of performance issues.
February 17, 2008 at 4:20 am
I’ve used it on two project now.
The first was a SQL Server 2000 staging database. It had about 20 tables and 50 stored procedures.. the tool made short work of it.
The second project has 1700 tables and 2000 stored procedures! (Not to mention views, indexes, etc)
It isn’t really a case of narrowing down the performance issue.. the goal of Visual Studio for DB Pro is to give you a dynamic view of your codebase, much like C#. The problem is that in order to hold this view, it has to check the implication of every change you make to your schema, which in effect rebuilds the entire database.