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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.