How to 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 don't 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 <DB> clause which SMOScript doesn't add to exported file in contrast to the manuall export as script sproc for CREATE.

Answer 1

Thanks to all above for help and leaving this here for other sqlcmd noobs like myself to find. Works on sql server 2005. objectName being a procedure, view etc name.

:reset

-- :setvar ObjectName "objectName"  -- works
:setvar ObjectName objectName  -- works too
declare @sql varchar(max);
set @sql = 'select text from dbo.syscomments where id = object_id(upper("' + '$(ObjectName)' + '"))';
-- exec sp_helptext $(ObjectName)  -- quick n easy but gets chopped to 256 width
/*   4000 byte limit
set @sql = 
   'select view_definition 
    from information_schema.views 
    where upper(table_name) = upper("' + '$(ObjectName)' + '")';
*/
:out $(ObjectName).sql

exec(@sql)
go

:out stdout

Answer 2

Adding the SET NOCOUNT ON did indeed eliminate the need for the line_del.bat, but replacing syscomments with sys.sql_modules resulted in each stored procedure being truncated to 258 characters. So for the best results the code I used was:

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 text from dbo.syscomments WHERE id = OBJECT_ID('%%a')" -o "%%a.sql"

Which worked, and did not need the use of the line_del.bat. What I didnt get when I did the manual export using the SSMS wizard (Tasks/Generate Scripts/Stored Procedures/Select All) was the:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

at the beginning of each .sql, and the trailing GO command also. Not incredibly important but something to note. Thanks to Max Gontar, Seansilver and Lee for your contributions! I can now automate the backup of the stored procedures in the database, and apply version control.

Answer 3

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"

Answer 4

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 5

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 6

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.