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 🙂