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

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

Labels: Microsoft SQL Server, SQL