There are four (more on that later) ways in SQL Server 2005 to get the create procedure script. Why does the title say 3.5, well that is because the INFORMATION_SCHEMA.ROUTINES view only returns the first 4000 characters. So if your proc is 5000 characters then you are out of luck (not really since you can use the other 3 methods)
So let’s start, we will be using the uspGetBillOfMaterials stored procedure in the AdventureWorks database
This is the same as in SQL server 2000 nothing new here
So this is a new view in SQL Server 2005, what we need is the definition column
WHERE object_id = OBJECT_ID('dbo.uspGetBillOfMaterials')
This is a new function in SQL Server 2005, just combine it with OBJECT_ID to get the proc code back
SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.uspGetBillOfMaterials'))
This is the problematic one, if the ROUTINE_DEFINITION is greater than 4000 characters then it will be truncated
WHERE SPECIFIC_NAME ='uspGetBillOfMaterials'
AND SPECIFIC_SCHEMA= 'dbo'