Retrieving Stored Procedures from SqlServer and version control them.
Here is a script that you could use to retrieve your stored procedures and store them in your custom folder structure.
what this script does:
- create a list of all stored procedures on disk (inc subdir)
- retrieve all stored procedures
- add some extra TSql around it ( like use database …)
- overwrite SP by name in your dir; IF this was not possible store the script in notsorted dir.
Hope you’ll like it.
@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
|
Posted in 
Tags: