Script All the Stored Procedures in The Database

In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”.

But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it -

To script All the Stored Procedures in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

Similarly,

To script All the Views in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'V'

To script All the Functions in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'FN'

For scripting all Triggers small modification is required, instead of sys.objects I joined the sys.triggers with sys.sql_modules.

To script All the Triggers in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.triggers as O ON M.object_id = O.object_id

Mangal Pardeshi
SQL MVP