how save SQL stored prodecures to .sql files via batch

Question

Hello! 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

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.

Answer 2

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 3

hello again, I've used sqlcmd and -E instead of user, pass. This works fine so far, just stored procedures longer than 4000 chars will have a line break

sqlcmd -E -S SERVER -d DB -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -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 sqlcmd -E -S SERVER -d DB -h-1 -Q "SELECT text from dbo.syscomments WHERE id = OBJECT_ID('%%a')" -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"

best regards sean

Answer 4

I added SET NOCOUNT ON to eliminate the need for line_del.bat. Also replaced syscomments with sys.sql_modules (SQL 2005). I could also have used the OBJECT_DEFINITION function but it was slower than sys.sql_modules.

sqlcmd -E -S SERVER -d DB -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S SERVER -d DB -h-1 -Q "SET NOCOUNT ON SELECT definition from sys.sql_modules WHERE object_id = OBJECT_ID('%%a')" -o "%%a.sql"