Get All Stored Procedures of Database in SQL Server

While browsing on msdn blogs from my Google reader, I found an interesting SQL script that I was previously looking for. The SQL script uses cursor to print all stored procedure in a database. You have to change your result pane to text view to see the result properly.


Here is the SQL script to print all stored procedure using cursor.

USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT s.name
FROM sysobjects s
WHERE type = 'P'
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_HelpText @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName
GO

You can also get the same result using sysobject.

SELECT [text]
FROM 
sysobjects o 
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'

For more SQL coding tips and tricks, subscribe now.