Home » blog » Henry Huey's blog

SELECT-a-script - view text of all stored procedures

Posted February 5th, 2009 by Henry Huey

This example shows a SELECT statement that generates a script to dump the sp_helptext output for all stored procedures and functions. Run it with the output set to text in SQL Server Management Studio; copy the results to a new query window and execute the script, again with output to text. The "SET NOCOUNT" statements are included so that there won't be an extra row like this: (856 row(s) affected) at the end of the generated script, but you can omit them and just remember to leave it out.

This technique can be used whenever you have a repeating process that doesn't lend itself to a set-oriented query (like running a stored procedure against selected values from a table).

SET NOCOUNT ON
GO

SELECT 'sp_helptext ' + [SPECIFIC_NAME]
+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE [ROUTINE_TYPE] = 'PROCEDURE' OR [ROUTINE_TYPE] = 'FUNCTION'
ORDER BY [ROUTINE_NAME]

GO

SET NOCOUNT OFF
GO

The generated script looks like this:

--------------------------------------------------------------------------------------------------------------------------------------------------
sp_helptext BAEAccountPendingDelete
GO
sp_helptext BAEAccountPendingGetAll
GO
sp_helptext BAEAccountPendingInsert
GO
etc.

Henry Huey's blog | login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

there's no INFORMATION_SCHEMA.TRIGGERS...

On March 11th, 2010 Henry Huey says:

...so use this query if you're interested in viewing all triggers:

SELECT 'sp_helptext ' + [name]
+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.triggers
ORDER BY [name]

login or register to post comments

It's even nicer without all the GO statements

On March 12th, 2010 Bruce Wilson says:

SET NOCOUNT ON
GO
SELECT 'exec sp_helptext ' + [SPECIFIC_NAME]
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE [ROUTINE_TYPE] = 'PROCEDURE' OR [ROUTINE_TYPE] = 'FUNCTION'
ORDER BY [ROUTINE_NAME]
GO
SET NOCOUNT OFF
GO

--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.

login or register to post comments

Just as an aside, you can

On March 12th, 2010 Paul Bradshaw says:

Just as an aside, you can get the info directly without requiring sp_helptext too... the following works on SQL Server 2005 and above:

SELECT p.[name] AS ProcedureName, s.[definition] AS ProcedureDefinition
  FROM sys.procedures p
       INNER JOIN sys.sql_modules s ON p.[object_id] = s.[object_id]
 WHERE (p.[name] NOT LIKE 'aspnet_%' AND p.[name] NOT LIKE 'dt%')
 ORDER BY p.[name]

 This works for views and functions as well (with some modifications)... and is more useful if doing something programatic.

login or register to post comments