Recompile Views/SPs from DOS from .SQL Files.

,

This script is very helpful for anyone trying to recreate particular SQL Server Database Versions for Testing.  It allows you to automatically recompile any stored procedures or views saved with drop, create and permission statements.  The scripts must all be sitting in one directory and end in the same extension.  I have found this very useful in the development lifecycle or when trying to release code in batches.

For those of you that save your stored procedures or views as .sql files OFF the server (say in VSS?!?) - if you save them in the following format:

if [codename] exists ... then drop [codename]
go

create [procedure/view] as

begin

[.....code goes here....]

end
go

grant [execute/select] on [codename] to [user/group]
go

then you can use the code below FROM DOS to recompile all of your stored procedures and/or views if you check out a particular copy or version set to a universal directory:

cd [directory]
for %f in (*.sql) do osql -S [SERVERNAME] -d [DBNAME] -U [LOGIN] -P [PWD] -i %f > %f.log

This will compile each .sql file in the directory to the server specified and write the success or failure of each to a .log file in the same directory.

Rate

Share

Share

Rate