a coder named Floris

@echo off
rem This script has been created to extract all Stored Procedures,
rem and place them in your version controlled directory structure.
rem Any script not stored will be placed in the 'notsorted' dir.

rem This scripts uses 'Intergrated security' the -E of osql. If you want to use log and pass use -U pass -P pass
rem ######################
set serverName= >>HostNameHere<<
set dbName= >>DataBaseNameHere<<
rem ######################


IF EXIST tmp rd tmp /s /q
IF EXIST notsorted rd notsorted /s /q

IF NOT EXIST tmp mkdir tmp
IF NOT EXIST notsorted mkdir notsorted

rem Create List of existing scripts (handy for version controlling)
dir *.sql /s /b >orgScripts.txt

osql -E -S %serverName% -d %dbName% -h-1 -Q "SET NOCOUNT ON;SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o ".\tmp\sp_list.txt"


rem Store one SP per file.
for /f %%a in (.\tmp\sp_list.txt) do (


	REM write to *._sql to match
	SQLCMD -S %serverName% -d %dbName% -Q"EXEC sp_helptext '[dbo].[%%a]'" -W -h"-1" -o .\tmp\%%a._sql


	REM This is basicly a template that comes at the start of the script.
	echo USE [%dbName%] > .\tmp\%%a._t_sql
	echo GO >> .\tmp\%%a._t_sql
	echo SET ANSI_NULLS ON >> .\tmp\%%a._t_sql
	echo GO >> .\tmp\%%a._t_sql
	echo SET QUOTED_IDENTIFIER ON >> .\tmp\%%a._t_sql
	echo GO >> .\tmp\%%a._t_sql

	rem Merge template and SP.
	copy .\tmp\%%a._t_sql +.\tmp\%%a._sql .\tmp\%%a.sql  >nul


	del .\tmp\%%a._sql
	del .\tmp\%%a._t_sql


	REM add last LINE.
	echo GO >> .\tmp\%%a.sql
)

rem Overwrite stored procedures with the ones in version control.
for /f %%a in (.\tmp\sp_list.txt) do (
	call :merge %%a.sql
)

goto end

:merge
set _Fndcmd=find /I "%1" orgScripts.txt
set _writes=0

FOR /f "tokens=*" %%b in ('%_Fndcmd%') do (
	IF EXIST %%b (
		IF EXIST .\tmp\%1 (
			rem DEBUG LINE:
			rem echo copy %1 to %%b
	
			rem maybe you need to remove readonly (use Attrib -r)
 			copy ".\tmp\%1" "%%b" >nul			
			set _writes=1
		)
	)
)
rem Is a file stored?
IF %_writes% EQU 0 (
	echo Not stored! [%1]  
	copy .\tmp\%1 .\notsorted\%1 >nul
)

goto :eof


:end
Rem remove temp dir.
rem rd tmp /s /q
del orgScripts.txt