how save SQL stored procedures to .sql files via batch

Question

I woud like to save my MS SQL Server 2005 stored procedures to .sql files automatically (would prefer a tool which I can call via .bat) so I dont have to click each single sproc manually and save it.

I have already found SMOscript from devio IT, but it gathers all tables and sproc which takes some time. Is there any similar tool where I can define which sproc(s) to export? Also I'm missing the USE clause which SMOScript doesn't add to exported file in contrast to the manuall export as script sproc for CREATE.

best regards sean

Answer 1

Create batch file with script (sorry about formatting, but it's really should be inline to execute batch):

osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o "sp_list.txt"
for /f %%a in (sp_list.txt) do osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a'" -n -o "%%a.sql"

Name it "run.bat". Now, to execute batch use params:
run.bat [username] [password] [servername] [database]
on example:
run.bat sa pwd111 localhost\SQLEXPRESS master
first all stored procedure names will be stored in file sp_list.txt, then one by one in separate script files. The only issue - last line of each script with result count - I'm workin' on it :)

edited: bug in query fixed

Removing "Rows affected" line
Ok, now we need to create one more batch:

type %1 | findstr /V /i %2  > xxxtmpfile 
copy xxxtmpfile %1 /y /v
del xxxtmpfile

Name it "line_del.bat". See, the first param is file to process, 2nd - string to search lines for removing. Now modify the main batch (again, sorry about formatting):

osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o "sp_list.txt"
call line_del sp_list.txt "rows affected"
call line_del sp_list.txt "row affected"
for /f %%a in (sp_list.txt) do osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a'" -n -o "%%a.sql" 
for /f %%a in (sp_list.txt) do call line_del %%a.sql "rows affected"
for /f %%a in (sp_list.txt) do call line_del %%a.sql "row affected"

See related articles:
Simple programming commands in a batch environment
osql Utility
MSSQL: How do you script Stored Procedure creation with code?
Delete certain lines in a txt file via a batch file

:) you may notice, last two are from SO!

Answer 2

There is an alternative in SQL Server Management Studio, scripting the database...

Expand the Object Explorer view to find the database, right click and select "Tasks : Generate Scripts"

From there you can script all object, just stored preocedures, of anything in between. There are quite a few options on one page, though the main one I change is: - "Include IF NOT EXISTS"

By making that option "FALSE" then you just get a whole list of CREATE statements.

You can then choose to script the objects to a new query window, or a file.