sql server - Generating create stored procedure script using SQL syntax only

Question

I am aware it is possible in SQL Server Management Studio to generate a create stored procedure script using the Object Explorer (right click on stored procedure, "Script stored procedure as...", Create To)

Is it possible to generate a create script string using SQL syntax only?

declare @createSPstring varchar(max)
/*
insert code to generate the create stored procedure string and put it into @createSPString...
*/
select @createSPstring

question from:https://stackoverflow.com/questions/65933441/generating-create-stored-procedure-script-using-sql-syntax-only

Answer 1

You can get the full text from the view: sys.sql_modules

However your selectmight not get the full code, since a nvarchar(maX) is cut of in SSMS.

One option is to use print to print each row from the definition. Here I split the definition on char(13) to get each row and use a cursor (yes i know) to print each row.

DECLARE @createSPstring VARCHAR(MAX)
-- get definition of procedure "test"
SELECT @createSPstring = definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('test')

-- Declare cursor - split definition on line break
DECLARE rows CURSOR FOR
    SELECT [value] row
    FROM STRING_SPLIT(@createSPstring, CHAR(13))

DECLARE @row NVARCHAR(MAX)
OPEN rows

FETCH NEXT FROM rows INTO @row

WHILE @@fetch_status = 0
BEGIN
    --Print each row
    PRINT REPLACE(@row,'char(10)','')
    FETCH NEXT FROM rows INTO @row
END