SYSK 93: SQL 2005 – How to Script Database One Object per File

In SQL 2000, there was a feature many of us loved and used heavily – ability to script all database objects one per file.  This allowed the scripts to be stored in a source control tool for versioning purposes.  Unfortunately, this feature is missing from SQL 2005 Management Studio – the scripts are now saved in one file (not one object per file). 

 

One way to get around this limitation, is to create a database project in Visual Studio (under Other Project Types), add it to Source Control, and connect it to your database by using the wizard.  Then, in the Server Explorer, expand tables, stored procs or other categories you want to script, right mouse button click on each item and choose “Generate Create Script To Project”. 

 

Alternately, you can create a new project right in SQL Management Studio, add it to Source Control, script each object to a file by right mouse button clicking on it and choosing Script As -> Create To -> File option.  Then add the file to the project by choosing Project -> Add Existing item menu option.

 

Yes, it’s a lot of work, but once it’s done, you can maintain individual (object per file) scripts in Visual SourceSafe…  You should be able to script that, but I haven’t tried…

 

If anybody has a better/easier way to accomplish the same, don’t be shy – post your suggestion J