If a stored procedure is modified 10 times , i want to log or track it in a table in a column in 10 rows from initial stored procedure to the latest that is 10 different versions of the same stored procedure , could any one suggest how to do this?
views:
72answers:
3
A:
I know I'm not directly answering your question, but did you consider using a source control tool for it? Like svn, source safe, ...
Gustavo
2009-05-14 10:02:17
svn is there, but still i need to store in a database the different versions of a single stored procedure, it should not be a manually process ,that is if 10 service packs are applied on production database for same stored procedures that is to say 10 different versions of stored procedures are applied.i want to track all these 10 versions in table in the database from the 1st version to 10 th version.
2009-05-14 10:11:32
A:
In case you are using dot net it will be a good idea to create a Database project where you can store the SP as well as scripts for all other objects and use some source control where you will be able to maintain version history.
Rashmi Pandit
2009-05-14 10:04:31
+1
A:
You don't say which version of SQL server you're using, but have you looked at DDL triggers?
Ed Harper
2009-05-14 10:25:14
In which case, DDL triggers are available to you - I have updated the documentation link to the 2005 version.
Ed Harper
2009-05-14 10:52:16
DDL Triggers are definitely a good idea. I blogged about using DDL triggers to propogate stored procedure changes in the following article - you can easily adapt the code to your logging: http://thehobt.blogspot.com/2009/03/synchronizing-changes-to-stored.html
Aaron Alton
2009-05-17 01:32:42