Check Stored Proc into source control: What to check in?

I have some SPs that I want to be under source control, but the question is what do I add?

Ideally I want some way to purely get the source of the SP, however all I can get is alter, create, etc scripts which I do not consider to be the raw source. The closest I can get is running sp_helptext 'mysp'.

Is there some way to purely get the source of an SP?

I am running SQL Server 2008 R2.

EDIT: I understand the usefullness of being able to grab what is in source control and update/deploy a SP, however I am firmly 开发者_如何学Pythonagainst this. This code is useful situationally, irrelevant when making diffs to see changes and generic (violates DRY principle. eg. SQL Server 2010 has a new way to create and drop sps.. need to update the 'source' for all my SPs?). If I wanted something like this I would be much more inclined to make a script that will deploy a SP to a server (eg. deploy dp_mysp prod).

Is there a way to get just the guts of an SP? Or is it actually stored as a create procedure script?

EDIT2:

Cheers guys.

I don't object to versioning settings and references (or deployment scripts which is the best analogy IMO) as they exist in one place and are reusable nuggets of goodness. The key sucky thing about this is the same deployment code exists in many places and must be maintained in many places. There is no dependency between this code and each sp, so it adds cruft to every sp. Why don't we add generic deployment code to every file in our solution so they are self deployable?

Anyway, I guess we will have to agree to disagree on this one. I am definitely being a purist and I don't think there is much practical benefit to what I want, but for me being super anal about development is what makes it fun :)

No-one helped me get closer to what I want than sp_helptext 'mysp' (and I am guessing it is impossible at this point) or convinced me I should be checking in something else, so I am going to leave the question unanswered.

Thanks again.

in my opinion what you'd want to put under source control would be a script containing an if exists(...) drop followed by a create procedure followed by the necessary permissions. this way you can revert back to exactly the way the sproc was at the time of entry in to source control.

remember that the sql statements themselves are not the only thing you should be keeping track of.

Do you have access to Visual Studio 2010? They merged DB Pro with the Developer edition giving you access to database projects and schema comparison. Using these tools, you can pull all tables, views, stored procedures, user defined functions, etc. in a file-based format with ease. Then you can store that database project in source control. You can even compare different revisions of the project to see what has changed, not to mention deploy to servers.

If you don't have this tool, then you are going to need to script out the procedures. At my previous job, we were fine with the create statements.

Create Database project in VS2010(ultimate) only and do schema compare with local database and maintain the repository in the TFS. Doing it this will maintain all the changes to SP in TFS

Before VS 2010, I used to have the script create a stub procedure if one does not yet exist, and then "alter procedure" with the actual code:

if not exists (select * from sys.objects where name = 'myProc' and type = 'P')
begin
    exec('create procedure myProc as print ''stub''')
end
GO
alter procedure myProc as 
....

This way, on an update you retain the procedure metadata - permissions, creation date etc. Also, if there is a syntax error in your script, you don't end up deleting the old version of the procedure from the database.

Something I've always done is kept scripts for creating stored procs. Just so that I can keep them under source control.

I include three sections in each stored proc script:

1. A little check at the top of the script to drop the stored proc if it exists (ends with GO)

2. The create of the stored proc (ends with GO)

3. Then a list of any of the grants for the stored proc. (GO is implied)

This type of script for creating a stored proc is great because you can run it several times in a row and it always works without throwing an error or messing anything up. It's a lot easier if you start off using this technique. If you're working backwards from an existing bunch of stored procs you can use SSMS to generate create scripts, just use the options to include a drop before the create. You also have to make sure to add in the grants at the end or every time you drop and recreate a sproc the users won't be able to access it anymore.

If I read your question correctly, you want only the body of the stored procedure and not the "create procedure" bit. Let me try to convince you that you want the whole thing. A couple of things that can happen before the guts are:

• An "execute as" clause
• The parameters for the stored procedure (and their defaults)
• A "recompile" clause

All of these affect how your stored procedure runs.

Like most other answerers I don't really agree with the reasoning behind what you're trying to do - however, if you're determined to go down this route you can get the rawest object definition possible of existing procedures from the INFORMATION_SCHEMA.ROUTINES table:

SELECT ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_NAME = '<sp_name>'

You can query this out into files with whatever programming language you prefer. You'll note that the database engine itself stores CREATE PROCEDURE...

One of the reasons I think this approach may be difficult to maintain is that it's worth remembering some session-level settings of the session in which a stored procedure is created affects how/whether the sp functions - e.g. the effect of the QUOTED_IDENTIFIER setting on stored procs using XML functions - you'll need some way to record/retrieve these settings per procedure.

You might have to wait for SQL 2011 (Denali)

Micheal Otey says:

"Juneau can also analyze a set of changes and generate a script that will update the database and all the objects affected by changes that you make in the development environment. Like Visual Studio, Juneau supports the concept of projects and can be integrated with source control using Team Foundation Server. One of the cooler features is Juneau’s ability to immediately jump to column definitions or show all the references for a given column. One of Juneau’s goals is to make the development environment consistent for both SQL Azure and the on-premises version of SQL Server."

http://www.sqlmag.com/article/sql-server/first-look-sql-server-code-named-denali

"Juneau", the VS2010 shell for SQL Server:

http://blogs.msdn.com/b/ssdt/archive/2010/11/08/welcome.aspx