How can I view the original SQL that created a stored procedure in SQL Server 2008?

Question

The title pretty much says it all.

How can I view the original SQL that created a stored procedure in SQL Server 2008?

Is this possible? I've been searching online for some leads, but I'm either missing correct vernacular or I'm just looking for something that can be found by some other means.

My basic problem is that I've got a SQL Server 2008 db here with a couple hundred stored procedures and I want to see what they are doing. I need to copy one and modify it slightly and then use it.

Answer 1

To get the definition

select object_definition(object_id('sp_help'))

Or in management studio right click the procedure and choose a scripting option.

Answer 2

Open up management studio and expand the database you are after. Inside of there is a programmability folder, expand that and you will see the stored procedures. Right click on one of them and select modify.

Answer 3

As long as it was not encrypted sp_helptext is the stored procured you want to show the text of any stored procedure

Answer 4

From a query window on the db you can execute sp_helptext YOURPROCEDURENAME It's a shorthand for what Martin described.

Answer 5

For any of the answers given so far, if there was any set up done - to create a #temp table that the proc depends on, for example - that won't exist in the results because SS stores the functional code for the proc definition, not all of the SQL used in the creation. Some things you might have to infer.

Answer 6

Of course if you were storing your sps in your source control as you should be doing, you would go there and look at it and even be able to see previous versions.