Execute Stored procedure scripts in bulk

Hi,
I have a folder containing about 150 stored procedures. Is there a way
- through batch or command file to execute all of them at
once..execute all files ending in .sql extension (inside a folder).

Its not possible to list each SP name in a command file or execute
each SP in management studio.

please suggest.

thanks

> I have a folder containing about 150 stored procedures. Is there a way
> - through batch or command file to execute all of them at
> once..execute all files ending in .sql extension (inside a folder).

A lowly Windows FOR command can help you here. The FOR command example
below will run all "sql" in the current folder and redirect stdout and
stderr output to a log file for later review:

FOR %f IN (*.sql) DO SQLCMD -E -S MyServer -d MyDatabase -I -i "%f" 2>&1
>>"sqlcmd.log"

You can also encapsulate this in command file for reuse. The difference is
that you need to precede the variables with a two percent signs instead of
one and pass the target server and database as command-line arguments:

FOR %%f IN (*.sql) DO SQLCMD -E -S %1 -d %2 -I -i "%%f" 2>&1 >>"sqlcmd.log"

Save the command as RunScripts.cmd in the same folder as your scripts and
execute with:

RunScripts MyServer MyDatabase

See Windows help for more information on FOR and other useful commands.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"tirath" wrote in message

> Hi,
> I have a folder containing about 150 stored procedures. Is there a way
> - through batch or command file to execute all of them at
> once..execute all files ending in .sql extension (inside a folder).
>
> Its not possible to list each SP name in a command file or execute
> each SP in management studio.
>
> please suggest.
>
> thanks