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

Please check https://stackoverflow.com/questions/10451146/what-is-the-sql-server-system-table-that-contains-information-about-stored-proce — Sergey, Jan 28 '21 at 08:31
That doesn't show the OP how to get the full definition, @Sergey . — Thom A, Jan 28 '21 at 09:18
FYI, object explorer uses [SQL Server Management Objects](https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/sql-server-management-objects-smo-programming-guide) to generate those scripts. — AlwaysLearning, Jan 28 '21 at 11:20

score 0 · Answer 1 · answered Jan 28 '21 at 12:55

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

score 0 · Answer 2 · answered Jan 28 '21 at 14:59

select cast (definition as xml ) from sys.sql_modules WHERE object_id = OBJECT_ID('test')

click on the result, you can get the sp with the same format you created. only problem is if your proc using > or < operators; it will be replaced with > <

you require to replace manually using ctrl+h

Generating create stored procedure script using SQL syntax only

2 Answers2