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, http://www.dotnetfunda.com/articles/article20.aspx 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, http://www.dotnetfunda.com/articles/article20.aspx,

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 :).
If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.