Need Script to compile n no of stored proc

Question

Need Script to compile n no of stored proc

Ahmad Osama

SSCertifiable

Points: 7676
More actions
September 25, 2008 at 7:40 am

#382831

Hi All,
I have to compile some 30-40 stored procedures at the client end.
Currently I am do this by compiling each proc one by one in a query window.
Is there a way to group all the proc in to script and run that script instead of compiling each and every proc
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

Ahmad Osama (9/25/2008)
Hi All,
I have to compile some 30-40 stored procedures at the client end.
Currently I am do this by compiling each proc one by one in a query window.
Is there a way to group all the proc in to script and run that script instead of compiling each and every proc

Generate them to a sql file and run them as a whole

Madhivanan

Failing to plan is Planning to fail

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

Madhivanan (9/25/2008)
Ahmad Osama (9/25/2008)
Hi All,
I have to compile some 30-40 stored procedures at the client end.
Currently I am do this by compiling each proc one by one in a query window.
Is there a way to group all the proc in to script and run that script instead of compiling each and every proc
Generate them to a sql file and run them as a whole

Thanks....but generating them to a sql file will also require compiling each one of them separately and then generate a script.

Also compiling more than 50 procedures is a tedious task...

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Gayathri.Varadarajan SSCrazy Points: 2080 More actions · Answer 3

use sqlcmd to run a batch file from the command prompt.

Copy all the procedures to a folder.

create a bat file for the below sqlcmd

sqlcmd -S

In your input text file add all the procedure names that are to be run as

:r proc1.sql

go

:r proc2.sql

go

Ps:You can find some application which will return all the filenames with *.sql to be filled in the input text

Hope this helps you.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Run the following query. It will generate the script you need. Change to "Result to Text" from "Result to grid" before you run it.

select 'sp_recompile ' + [name] + char(13) + char(10) + 'go' from sys.objects where type = 'P'

Copy and paste the output to a new query window. Then execute it.