store output of sp_helptext to local variable

Question

Post reply

store output of sp_helptext to local variable

johnsql-193053

SSCommitted

Points: 1657
More actions
March 20, 2008 at 10:10 am

#70893

Hi,
I have a stored procedure mySP and I'd like to use a local variable to store the ouput from sp_helptext to that variable. However, I got the error "... Incorrect syntax near "mySP'."
declare @v-2 varchar(8000)
set @v-2 = sp_helptext mySP
Is there is better way to do such and to avoid the error?
Thank in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ralph Curry SSC Enthusiast Points: 167 More actions · Answer 1

It's not pretty, but you might try something like this:

declare @t table(line varchar(8000))

insert @t exec sp_helptext 'myProc'

declare @ddl varchar(8000)

select @ddl = ''

select @ddl = @ddl + line

from @t

print @ddl

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

For some reason, this does not work for me. Keeps telling me that I have to "[font="Courier New"]declare the scalar variable @ddl[/font]"

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

duh. Its a typo, should be [font="Courier New"]@ddl[/font] on the select line.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ralph Curry SSC Enthusiast Points: 167 More actions · Answer 4

sorry about that. I've edited the original post to fix the error.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Thanks for the example Ralph. I was just mad at myself for not noticing the typo before I posted my reply...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

robert.mcleod SSChasing Mays Points: 618 More actions · Answer 6

I realize this post is old, but I found it handy for a problem I was working today.

One thing I added was to change the CREATE to an ALTER. This was to facilitate backing up an existing object.

Here is what my finished code looks like, should it help someone else.

SET NOCOUNT ON;

USE <DATABASENAME>;

DECLARE

@objName varchar(128);

SET @objName = <OBJECT NAME>;

/* Script out objects if they exist*/

IF EXISTS (SELECT *

FROM sys.objects

WHERE object_id

=

OBJECT_ID(@objName)

AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT',N'P'))

BEGIN

DECLARE

@t TABLE(line varchar(8000))

END;

INSERT INTO @t

EXEC sp_helptext @objName;

DECLARE

@ddl varchar(8000);

SELECT @ddl = '';

/* Change CREATE to ALTER because this object all ready exists */

SELECT @ddl = @ddl + REPLACE(line, 'CREATE procedure', 'ALTER PROCEDURE')

FROM @t;

PRINT @ddl;

seequl Newbie Points: 1 More actions · Answer 7

I also realize that this post is really old but below is a script I put together that could be helpful to others running across this post. This script finds the lines for every stored procedure or function.

IF OBJECT_ID('tempdb..#SP_Lines') IS NOT NULL

BEGIN DROP TABLE #SP_Lines END;

CREATE TABLE #SP_Lines (SPName varchar(200), Type varchar(4), LineNbr int, LineText text)

DECLARE @SPlines TABLE(Type varchar(4), LineText TEXT)

DECLARE @SP_name varchar(150)

DECLARE @SP_type varchar(4)

--LOOP through each SP

DECLARE dbfn_cursor CURSOR FOR

SELECT O.NAME, O.TYPE

FROM sys.objects O

INNER JOIN

sys.sql_modules S ON O.object_id = S.object_id

WHERE O.TYPE IN ('P','FN')

ORDER BY O.TYPE Desc, O.name

OPEN dbfn_cursor

FETCH NEXT FROM dbfn_cursor INTO @SP_name, @SP_type

WHILE @@FETCH_STATUS = 0

BEGIN

--SELECT @SP_name

INSERT INTO @SPlines (LineText)

EXEC sp_helptext @SP_name

INSERT #SP_Lines

SELECT @SP_name As SPName, @SP_type As Type, ROW_NUMBER () OVER(Order BY (Select 1)) As LineNbr, LineText

FROM @SPlines

DELETE FROM @SPlines

FETCH NEXT FROM dbfn_cursor INTO @SP_name, @SP_type

END

CLOSE dbfn_cursor

DEALLOCATE dbfn_cursor

SELECT *

FROM #SP_Lines

ORDER BY SPName, LineNbr

drew.allen SSC Guru Points: 76944 More actions · Answer 8

seequl wrote:

I also realize that this post is really old but below is a script I put together that could be helpful to others running across this post. This script finds the lines for every stored procedure or function.

There is no reason to use a CURSOR here. This can be done quite simply without the very expensive overhead from a CURSOR.

SELECT O.NAME, O.TYPE, OBJECT_DEFINITION(O.OBJECT_ID) AS ROUTINE_DEFINITION
FROM sys.objects O
INNER JOIN
sys.sql_modules S ON O.object_id = S.object_id
WHERE O.TYPE IN ('P','FN')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 9

drew.allen wrote:

seequl wrote:
I also realize that this post is really old but below is a script I put together that could be helpful to others running across this post. This script finds the lines for every stored procedure or function.
There is no reason to use a CURSOR here. This can be done quite simply without the very expensive overhead from a CURSOR.
SELECT O.NAME, O.TYPE, OBJECT_DEFINITION(O.OBJECT_ID) AS ROUTINE_DEFINITION
FROM sys.objects O
INNER JOIN
sys.sql_modules S ON O.object_id = S.object_id
WHERE O.TYPE IN ('P','FN')
Drew

If you use this method to display in a grid you should also make sure from the SSMS menu option Query\Options that "Retain CR/LF on copy or save" is checked.

Capture