Source control for existing stored procedures (Continued..)

Posted by in Sql Server category on for Advance level | Views : 12464 red flag

In my previous article,, I have demonstrated on how source control can be made available on stored procedures during the initial stages. Now, let us consider a scenario where source control was lacking for stored procedures for an online database which already was in production.
Please do read the complete article before implementing the same.

Now due to change requests/enhancements we may need to alter stored procedures and in order to keep track of all the modifications, we need source control. This source control can be accomplished either using DDL trigger (More about DDL trigger in my subsequent article(s)) or the way this article explains in a moment.

Consider we have a few stored procedures in a database, and as a DBA I need to track all the changes to any of the stored procedures. The challenge lies in writing the source code of a stored procedure to a file and the file name should be same as the stored procedure name. Once the files are available the same procedure as demonstrated in my earlier article, can be used to achieve the needful task with a minor change on the implementation procedure.

I need to make use of copy and paste functionality (People call this re-usability and one of my friend calls it code adaptability :) ). I need to open the stored procedure, copy all the code, open a new query editor paste the code and save the file name as whatever was my stored procedure''s name. This would have been an easier task if my database had just around 10-20 stored procedures. But, real-time database demands more stored procedures, multiples of 100 or may be more. In this scenario, it will be a tedious job to perform copy+paste on every stored procedure.

SQL Server 2005 comes up with a nice little tool, bcp. Yes, bcp meaning Bulk copy utility. I made use of this utility to accomplish my task. This utility has saved enormous amount of labor involved in using/adapting copy+paste functionality. Now is the time to get into the details.

Execute the following dynamic query in one of your sql query editor window.

select 'bcp "SELECT routine_definition from dpi_development.information_schema.routines where routine_name=' + '''' + routine_name + '''' + '" ' + 'QueryOut d:\dpi\' + routine_name + '.Sql -S alexis\sqlsrv01 -U dpi -P Value*() -c'

from information_schema.routines order by routine_name

The above query would fetch the routine definitions from the catalog view, information_schema.routines. By making use of bcp we then save the results to a file with the same name as the stored procedure name.

In the above query,

bcp – bulk copy utility
dpi_development – Name of the database
information_schema.routines – catalog view that holds definitions of all the stored procedures in a given database.
QueryOut – Parameter to bcp, which specifies the location where the output will be saved.
S – Parameter to bcp, which specifies the SQL Server 2005.
U - Parameter to bcp, which specifies the UserName used to connect to the server
P - Parameter to bcp, which specifies the Password used to connect to the server
c - Parameter to bcp, which specifies the character type. Leave blank to have the default character type.
T – Parameter to bcp, which is meant for a trusted connection to SQL Server.

The above query would fetch, the following output. However a glimpse of the same was shown below for the reasons of demonstration.

Now copy the records in the results pane and open the Visual Studio .NET 2005 command prompt as shown in the picture below.

Paste the results in the command prompt. One should see the screen as follows. Forget not to delete the files with the same name including extension at the location specified for the bcp parameter, QueryOut. Otherwise the bcp utility fails to save the file.

Now open the location, d:\temp to view the .sql files stored under the sp''s name.
Once the sql files are available, Create a new project, as explained in,,

Right click on the project name and choose Add -> Existing Item.

Browse to the directory where the .sql files have been saved as shown in the following figure and click on Add.

The solution explorer should now contain the newly added stored procedure and the status should be checked-out.

OK now we are all set for a usual source control. Forget not to save the file once the development/modification is done. Press F5 for the changes to take affect in the database.

Happy Development and safe source control :).
Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Deeraj Chakravarthy
Member Level: Starter
Member Status: Member
Member Since: 10/29/2007 1:00:04 AM
Country: India

Qualification: BCA, MScIS, MDSE, PMI-ACP

Login to vote for this post.

Comments or Responses

Posted by: Meg_s on: 7/9/2008
Hi Deeraj

The article for adding Source control for existing stored procedures was a very helpful starting point in setting up SourceSafe for me. But I did face one issue. All the stored procedures that were created using the bcp utility were truncated!

Has anyone had a similar problem? Possible solutions/workarounds??

Posted by: Pvbabu on: 11/4/2008
This is very nice article. I am having two issues
1) When I copy and paste query, error.
2) I have copyied bcp "SELECT routine_definition from information_schema.routines where routine_name= 'p_DeleteFund'" QueryOut C:\_Venu\Scripts\p_DeleteFund.Sql -S (local) -U sa -P 007 -T -C VSCommand promt. IT is asking of series of questions
The file is create but not copied stored procedure into file.
Can you please help me?
Posted by: Deeraj on: 11/10/2008
In your Line of Code the database name is missing. You should prefix information_schema.routines with yourdatabasename.information_schema.routines. Because bcp doesnot have a switch to specify database name separately.

So your query should be
bcp "SELECT routine_definition from <YOUR_DATABASE_NAME_GOES_HERE>.information_schema.routines where routine_name= 'inser_order'"
QueryOut C:\_Venu\Scripts\p_DeleteFund.Sql -S (local) -U sa -P 007 -T -C



Posted by: prashanthnpspincorg-1985 on: 8/27/2009
The file is getting created with the stored procedure name. But the file contents are missing .
Any guess what may be the problem ?

Login to post response

Comment using Facebook(Author doesn't get notification)