Generate SP or function definition in SQL Server 2005/2008

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.

Look at the TSQL below:

select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id

--P for Stored Procedure

--FN for scalar function

--IF for Inlined table function

--TF for Table function

--TR for trigger

where so.type in ('P','FN','IF','TF','TR')

--if you want defination of specific function or SP, include below condition too

--and so.name='YourSPorFunctionName'

Order by so.name,sc.colid

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com