Clever Geek Handbook

Get the text of a stored procedure in SQL Server

I am trying to store an old stored procedure to a string. When I use the following, I don't get any line breaks.

SELECT @OldProcedure = Object_definition(object_id)
FROM   sys.procedures
WHERE  name = @ProcedureName

Any suggestions on how to get the text of the stored procedure with line breaks?

I thought about using sp_helptext

thank

Update

I copy and paste the results from the results that show me one row.

As for the script, since I am storing the results in a db field. I am creating a tool that will generate procedures on the fly, but I wanted to create a history of them.

Update

Off Object_definition does what I want, but for some reason, when I copy it from the results, I get one line.

Declare @sql varchar(max) ;
SELECT @sql=Object_definition(object_id)
FROM   sys.procedures
WHERE  name = 'Test_QueryBuilder';

drop procedure Test_QueryBuilder
exec( @sql)
print @sql
+3


source to share


4 answers


I recently ran into the same question and did a quick and dirty script to get the definition of views, but the same could be used for stored procedures and functions.

DECLARE @Lines TABLE (Line NVARCHAR(MAX)) ;
DECLARE @FullText NVARCHAR(MAX) = '' ;

INSERT @Lines EXEC sp_helptext 'sp_ProcedureName' ;
SELECT @FullText = @FullText + Line FROM @Lines ; 

PRINT @FullText ;


It just uses sp_helptext

, as you suggested, captures its output in a table variable and concatenates all the resulting rows into a text variable. It also takes advantage of the fact that every row in the result set sp_helptext

includes a newline terminator, so there is no need to add it here.

From there you just use the variable as usual, print it, save it to some table, or do some manipulation with it. My specific use case was to create a helper stored procedure to drop a view and recreate it when its main tables change.

+5


source


I would highly recommend using "Script To" in SQL Server Management Studio:

enter image description here



I've used this in the past when dealing with old objects like Stored Procedures

.

+3


source


Two ways:

select name, object_definition(object_id) 
from sys.procedures

or

select object_name(object_id), definition
from sys.sql_modules as m
join sys.procedures as p
   on m.object_id = p.object_id
+2


source


It sounds like you want to log the procedure text every time it changes. You can do this automatically with DDL Trigger . Unlike DML triggers, which fire when changes are made to data, they trigger changes to the database.

Create a table to store the audit log

CREATE TABLE DDLAudit_Procedure_log (event_instance xml);

Create trigger to populate audit log table

CREATE TRIGGER trg__DDLAudit_Procedure
ON DATABASE
FOR
  CREATE_PROCEDURE
 ,ALTER_PROCEDURE
AS
BEGIN
  INSERT DDLAudit_Procedure_log
        (event_instance)
  SELECT EVENTDATA();
END

After creating your automated routines, check the log

SELECT *
FROM DDLAudit_Procedure_log
WHERE event_instance.value('(//ObjectName)[1]','sysname') = 'MyAutoGeneratedProc'
ORDER BY event_instance.value('(//PostTime)[1]','datetime')

This is a simple example. You can view the content EVENTDATA()

in the trigger to filter for specific tables. The MSDN help page has more details.

+1


source






More articles:


All Articles