CODEWITHSUNDEEP
sqlsql-serverstored-procedures
H20rider
H20rider

Reputation: 2282

Get the text of a stored procedure in SQL Server

I am trying to save an old stored procedure into a string. When I use the following I do not get any line breaks.

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

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

I was thinking of using sp_helptext

Thanks

Update

I am copying and pasting the results from the results which will show me a single line.

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

Update

Turned out the Object_definition does what I want, But for some reason when I copy it from the results I get a single 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

Upvotes: 27

Views: 95531

Answers (5)

udorb b
udorb b

Reputation: 149

recommend to "sp_helptext" to get correct format

Upvotes: 2

Ben Thul
Ben Thul

Reputation: 32737

Two ways:

select name, object_definition(object_id) 
from sys.procedures

or

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

Upvotes: 45

Anon
Anon

Reputation: 10908

It sounds like you want to log the text of a procedure every time it changes. You can do this automatically with a DDL Trigger. Unlike DML triggers, which fire on changes to the data, these will fire on changes to the database.

Create a table to hold the audit log

CREATE TABLE DDLAudit_Procedure_log (event_instance xml);

Create a trigger to populate the 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 your autogenerated procedures are created, 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 examine the contents of EVENTDATA() within the trigger to filter for specific tables. The MSDN help page has more detail.

Upvotes: 3

Alejandro
Alejandro

Reputation: 7819

I've recently come across the same question and made a quick and dirty script to get the definition of views, but the very same thing could also work 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 simply uses sp_helptext as you suggested, grabs its output in a table variable and concatenates all the resulting lines into a text variable. It also uses the fact that each line in the sp_helptext result set includes the trailing new line character, so no need to add it here.

From there on, you just use the variable as you would do normally, print it, save to some table or do some manipulation on it. My particular use case was to build a helper stored procedure to drop a view and recreate it when modifying its underlying tables.

Upvotes: 19

BrianAtkins
BrianAtkins

Reputation: 1349

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

enter image description here

I have had great use of this in the past, when dealing with old objects like Stored Procedures.

Upvotes: 6

Related Questions