Generate SP or function definition in SQL Server 2005/2008

Monday, May 17, 2010 |

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 as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on
--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
Order by,sc.colid

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of