Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Version Control your Stored Procedures


Version Control your Stored Procedures

Author
Message
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 1846
Comments posted to this topic are about the item Version Control your Stored Procedures
irozenberg
irozenberg
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 145
Just a small suggestion - please consider EXTENDED properties for DB objects (including Stored Procedures), by using them in a key-value fashion you could avoid a lot of text processing.

Regards from DownUnder.
jcaradocdavies
jcaradocdavies
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 31
Just a small concern...

As far as experience and memory serves, sp_helptext is not a reliable source for recreating SQL server objects. SQL management objects are the preferred source.

Changes to an object and/or metadata may render sp_helptext outdated - the script used to create the original object may not reflect the object in its current state. I seem to remember a case where the sp_helptext record was blatantly incorrect after a (?) designer edit.

Does anyone have a concise view of when this may occur, and whether stored procedures are for practical purposes immune to these cases?
Phil Factor
Phil Factor
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1069 Visits: 2953
I really enjoy seeing scripts like this, since the whole topic of scripting is fascinating. What I really like about this is that someone has gone ahead and brewed a solution that fits their requirements like a glove. It is neat, because it will work with any version of SQL Server 2000+.

I wouldn't like anyone to think that this is a recommended general solution, though. SMO is the way to go! (I still occasionally use DMO)


Best wishes,

Phil Factor
Simple Talk
mike.renwick-894639
mike.renwick-894639
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 190
I also had similar issues with sp_helptext and eventually found a way of scripting objects using
Microsoft.SqlServer.Management.Smo.Scripter in Powershell.. this has the advantage that it handles all types of objects including everything from logins to linked-servers, mail profiles, etc, and can take advantage of easy iteration possible in powershell.

I've written a skeleton (and, disclaimer; is not warranted in any way) ps script that captures relevant source/schema stuff in SQL Server, if anyone's interested. Obviously it can be adjusted to taste for extended properties etc (which I turn off by default) and should be tweaked to expose specific properties relevant to only certain objects.

Link here
http://wp.me/pje2P-l

I also have a wrapper script that iteratively runs it for each database on the server, and a server-objects script that captures mail profiles, accounts etc, for anyone interested.

Regards,
Mike
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20789 Visits: 32385
I'm with Phil on this one. It was a good read and an interesting solution, but way more difficult than needed since SMO can do so much of the work.

That said, yee hah! for so much discussion around getting database code into source control. I think this is a huge whole in lots of shops these days.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7467 Visits: 8415
Or you can simply use ApexSQL Edit, which has some very spiffy built-in source code control functionalilty - among many other features.

Disclaimer: I have a close relationship with Apex, use their products and recommend them to my clients. Also, if you care to you can mention TheSQLGuru sent you you will get a discount and my daughter will get a few coins for her college fund.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
baxterr
baxterr
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 218
Good article. I always like to see/hear how others are using version control on their databases. Currently, we version all objects not just stored procedures, functions and triggers. There are inherent issues with this obviously, but we have tried to figure out ways around them that fit our particular needs. We are even working on a way to create automatic builds of our database changes to a testing environment without using compare tools. I'd love to hear if others have successfully used a version control solution to maintain database updates as well.

Thanks again for the article

J. Baxter
mike.renwick-894639
mike.renwick-894639
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 190
Automated deployments of stored-procs is relatively easy, but anything that involves a table schema change for example kind of makes the whole approach a bit complex and prone to problems. Also, it becomes much harder if you are building routines that need to be created in a specific order- we've toyed with creating dependencies but never got it working satisfactorily.

More useful for us in our small shop was detecting unauthorised/unexpected changes to our database objects, and providing a good audit of objects altered to tie into our change control process.

Using SMO we've been able to version most objects quite easily and build a daily source copy and an associated job to compare the output to a VC'd baseline, noting differences.

It's also made capturing changes between a development environment and production environment a lot easier- script both to two folders and run a winmerge or similar comparison tool to see what ammendments have been made- although as noted above, syncing the changes almost always is easier to handle the "long" way for our relatively simple db.
Bill Galashan
Bill Galashan
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 217
I have to agree with some of the comments here particularly on the principal on version control however and at the risk or creating some controversy, I would question the value of the solution provided other than it fitted exactly what one person required and it managed to generate some discussion.

Phil was spot on with recommending that SMO was the way to go and while I agree with that for this particular problem my underlying point is that the solution to a problem should really be based on sound technical knowledge of the tool set and the most appropriate elements of that tool set to be used to provide the solution.

Powershell was also mentioned and this in conjunction with SMO/SQLPSX can give you a scripter in one line of code. You cant get much simpler than that and while not very useful as it stands it does serve as an example of the value in knowing what is available.

-- Script all tables in the adventureworks database on the default instance on the local server
Get-SqlDatabase "(localserver)" "adventureworks" | Get-SqlTable | Get-SqlScripter

Articles like this just make me question just how much knowledge the writer has with the tool set we all use on a day to day basis.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search