Posted by Erland Sommarskog on 08/22/07 21:52

downwitch (downwitch@gmail.com) writes:
> I've run as many keyword combinations searching for this as I can
> think of, but cannot find a simple answer.
>
> The situation: I have a database that is mid-conversion/redevelopment.
> I frequently rebuild my dev copy from the production version, which
> requires a bunch of DDL script, and then adding in all the stored
> procedures necessary to the new version only. Each sp may have changed
> since the last time it was run, so I can't exactly keep them all
> concatenated in a single file or anything like that.
>
> So the only way I can see to execute each and every one of these saved
> script files is to open it up in SSMS and hit F5 for execute. This is
> a silly waste of time, it seems to me, and hardly foolproof. Is there
> not some simple way to run every script in a project? What purpose
> does a project serve, otherwise?

SQL code should be kept under source control, just likely any other code,
and deployment should be done from the version-control system.

Having said that, there are a couple of options you consider. One is
SQL Compare from Red Gate, although they currently have no direct support
for Source Control. The latest version supports comparing a database to
folder structure on disk, though.

A much more simple-minded way is to get a list of all stored procedures
you need to load. Put that in a text file, and then use a text editor
with good find-replace capabilities to transform the list to a BAT
file that runs SQLCMD to load the files. Just make sure that you run
SQLCMD with the -I option, so that you run with QUOTED_IDENTIFIER ON.


--
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]