{"id":354,"date":"2011-08-31T07:56:53","date_gmt":"2011-08-31T06:56:53","guid":{"rendered":"http:\/\/floris.briolas.nl\/floris\/?p=354"},"modified":"2011-08-31T07:56:53","modified_gmt":"2011-08-31T06:56:53","slug":"retrieving-stored-procedures-from-sqlserver-and-version-control-them","status":"publish","type":"post","link":"https:\/\/floris.briolas.nl\/floris\/2011\/08\/retrieving-stored-procedures-from-sqlserver-and-version-control-them\/","title":{"rendered":"Retrieving Stored Procedures from SqlServer and version control them."},"content":{"rendered":"<p>Here is a script that you could use to retrieve your stored procedures and store them in your custom folder structure.<\/p>\n<p>what this script does:<\/p>\n<ol>\n<li>create a list of all stored procedures on disk (inc subdir)<\/li>\n<li>retrieve all stored procedures<\/li>\n<li>add some extra TSql\u00a0around\u00a0it ( like use database &#8230;)<\/li>\n<li>overwrite SP by name in your dir; <strong>IF<\/strong> this was not possible store the script in <strong><em>notsorted<\/em><\/strong> dir.<\/li>\n<\/ol>\n<p>Hope you&#8217;ll like it.<\/p>\n<table border=\"1\">\n<tbody>\n<tr>\n<td>\n<pre>@echo off\r\nrem This script has been created to extract all Stored Procedures,\r\nrem and place them in your version controlled directory structure.\r\nrem Any script not stored will be placed in the 'notsorted' dir.\r\n\r\nrem This scripts uses 'Intergrated security' the -E of osql. If you want to use log and pass use -U pass -P pass\r\nrem ######################\r\nset serverName= &gt;&gt;HostNameHere&lt;&lt;\r\nset dbName= &gt;&gt;DataBaseNameHere&lt;&lt;\r\nrem ######################\r\n\r\n\r\nIF EXIST tmp rd tmp \/s \/q\r\nIF EXIST notsorted rd notsorted \/s \/q\r\n\r\nIF NOT EXIST tmp mkdir tmp\r\nIF NOT EXIST notsorted mkdir notsorted\r\n\r\nrem Create List of existing scripts (handy for version controlling)\r\ndir *.sql \/s \/b &gt;orgScripts.txt\r\n\r\nosql -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\"\r\n\r\n\r\nrem Store one SP per file.\r\nfor \/f %%a in (.\\tmp\\sp_list.txt) do (\r\n\r\n\r\n\tREM write to *._sql to match\r\n\tSQLCMD -S %serverName% -d %dbName% -Q\"EXEC sp_helptext '[dbo].[%%a]'\" -W -h\"-1\" -o .\\tmp\\%%a._sql\r\n\r\n\r\n\tREM This is basicly a template that comes at the start of the script.\r\n\techo USE [%dbName%] &gt; .\\tmp\\%%a._t_sql\r\n\techo GO &gt;&gt; .\\tmp\\%%a._t_sql\r\n\techo SET ANSI_NULLS ON &gt;&gt; .\\tmp\\%%a._t_sql\r\n\techo GO &gt;&gt; .\\tmp\\%%a._t_sql\r\n\techo SET QUOTED_IDENTIFIER ON &gt;&gt; .\\tmp\\%%a._t_sql\r\n\techo GO &gt;&gt; .\\tmp\\%%a._t_sql\r\n\r\n\trem Merge template and SP.\r\n\tcopy .\\tmp\\%%a._t_sql +.\\tmp\\%%a._sql .\\tmp\\%%a.sql  &gt;nul\r\n\r\n\r\n\tdel .\\tmp\\%%a._sql\r\n\tdel .\\tmp\\%%a._t_sql\r\n\r\n\r\n\tREM add last LINE.\r\n\techo GO &gt;&gt; .\\tmp\\%%a.sql\r\n)\r\n\r\nrem Overwrite stored procedures with the ones in version control.\r\nfor \/f %%a in (.\\tmp\\sp_list.txt) do (\r\n\tcall :merge %%a.sql\r\n)\r\n\r\ngoto end\r\n\r\n:merge\r\nset _Fndcmd=find \/I \"%1\" orgScripts.txt\r\nset _writes=0\r\n\r\nFOR \/f \"tokens=*\" %%b in ('%_Fndcmd%') do (\r\n\tIF EXIST %%b (\r\n\t\tIF EXIST .\\tmp\\%1 (\r\n\t\t\trem DEBUG LINE:\r\n\t\t\trem echo copy %1 to %%b\r\n\t\r\n\t\t\trem maybe you need to remove readonly (use Attrib -r)\r\n \t\t\tcopy \".\\tmp\\%1\" \"%%b\" &gt;nul\t\t\t\r\n\t\t\tset _writes=1\r\n\t\t)\r\n\t)\r\n)\r\nrem Is a file stored?\r\nIF %_writes% EQU 0 (\r\n\techo Not stored! [%1]  \r\n\tcopy .\\tmp\\%1 .\\notsorted\\%1 &gt;nul\r\n)\r\n\r\ngoto :eof\r\n\r\n\r\n:end\r\nRem remove temp dir.\r\nrem rd tmp \/s \/q\r\ndel orgScripts.txt<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u00a0around\u00a0it ( like use database &#8230;) overwrite SP by name in your [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_newsletter_tier_id":0,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[3],"tags":[15],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p61yPs-5I","_links":{"self":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts\/354"}],"collection":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/comments?post=354"}],"version-history":[{"count":1,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts\/354\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts\/354\/revisions\/355"}],"wp:attachment":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/media?parent=354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/categories?post=354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/tags?post=354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}