Posted by Erland Sommarskog on 02/28/06 01:06

Ted O'Connor (toconnor@gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
>
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
>
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
>
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]