Screen dump of all SQL Server database stored procedures-CodePudding

I was working with T-SQL recently and I wanted a certain functionality in one of the stored procedure, problem, I can't remember what is the stored procedure name, I handcrafted this:

DECLARE @storedProcsDumpTBL TABLE([SP_DUMP_TEXT] nVARCHAR(500))
DECLARE @firstValue AS VARCHAR(20)
DECLARE @prefix AS VARCHAR(20)
DECLARE @storedProcFullName AS VARCHAR(100)
DECLARE @storedProcNameTBL AS VARCHAR(100)

DECLARE hammerCursor CURSOR FOR
    SELECT name 
    FROM sys.procedures
    ORDER BY 1

OPEN hammerCursor

FETCH NEXT FROM hammerCursor INTO @storedProcNameTBL

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC xp_sprintf @storedProcFullName OUTPUT, N'dbo.%s',@storedProcNameTBL

    INSERT INTO @storedProcsDumpTBL([SP_DUMP_TEXT]) 
    EXEC sp_helptext @storedProcFullName

    FETCH NEXT FROM hammerCursor INTO @storedProcNameTBL
END

CLOSE hammerCursor
DEALLOCATE hammerCursor

SELECT * FROM @storedProcsDumpTBL

it kind of served my purpose, but is there anything better?

CodePudding user response：

How about

select object_definition(object_id) ddl
from sys.procedures