Friends, exists some trick for the sp_helptext doent show the word text and the line of ''-''????

Question

Post reply

Friends, exists some trick for the sp_helptext doent show the word text and the line of ''-''????

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
March 28, 2005 at 10:27 am

#66079

when i use the sp_helptext with a name of procedure it shows the word 'text' and a line of '-' and next the code but i need that only it throwes the code for example
sp_helptext cp_Findcount
the queryanalizer shows me this
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Create proc CP_FindCount
as
begin
and so and so so
well, i need that it shows me only the code and without the word text and without the line of '-'
is it possible? exists some set to do that?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

In query analyzer, Tools/Options, in Results tag, clear Print column Headers (*) check box.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

If you're not trying to do this in a script and just see or edit what's in the proc, open Query Analyzer, press [f8] to view the object tree, find the proc, right click on the proc, and select what you want to do (edit on screen, print to screen as a "CREATE", save to file, etc, etc).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 3

Thanks but i need to do with a command like a set nocount on for example

well i am trying to do a procedure to use it instead of the sp_help

this is it

CREATE PROC CP_SCRIPT

@procedure varchar(255)

as

begin

SET NOCOUNT ON

print " if exists (select name from sysobjects where name = '" + @procedure + "' and type ='p')"

print " Drop Procedure dbo." + @procedure

print " go "

print ""

exec sp_helptext @procedure

print ""

print " go "

print " grant exec on dbo." + @procedure + " to public "

print " go "

end

But it shows me the word 'text' and a line of '-' and i dont know how i can avoid that it shows that

thanks

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 4

sp_helptext is a procedure that returns a dataset containing one row for each line in the original procedure declaration.

If you do something like this, you can capture that output in a temporary table:

create table #rdf1 (Text varchar(7000))

insert into #RDF1
EXECUTE sp_helptext sp_myProc

Then, it's a simple matter of looping through the result set and printing each line out.

Alternately, add an identity column to the temp table, then insert the entire routine you want into it. INSERT the stuff you're printing into the temp table, inserting the output from the sp_helptext proc where it belongs. Then

SELECT text
  from #rdf1
 order by lineno

(assuming you named your identity column "lineno").

R David Francis

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

I don't understand exactly why you are trying to do this BUT...

Instead of redeveloping the wheel... STEAL!!!! Copy sp_HelpText from the Master database and make the following changes...

Replace all occurances of "sp_HelpText" with "sp_HelpProc"
Change the following code...
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
... to the following...
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
,@MyLine NVARCHAR(255) --ADDED
,@MyCounter INT --ADDED
,@MyMax INT --ADDED
Change the following code...
select Text from #CommentText order by LineId
... to the following...
--===== All this stuff added (without adding yet another cursor) =====
SELECT @MyMax = MAX(LineID) FROM #CommentText
SELECT @MyCounter = 1
WHILE @MyCounter <= @MyMax
BEGIN
        SELECT @MyLine = [Text] FROM #CommentText WHERE LineID = @MyCounter
        PRINT @MyLine
    SET @MyCounter = @MyCounter + 1
    END
--===== End of added stuff
--select Text from #CommentText order by LineId --Removed

Save the new "sp_HelpPROC" stored procedure where you want it and use it instead of sp_HelpText in your code as follows:

EXEC sp_HelpPROC @Procedure

It will do exactly the same as sp_HelpText including all the wonderful built in error checking except it will print to screen without the column name of "TEXT" or those annoying little dashes. If it were me, I'd spend the additional time to replace all the cursors the code contains...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)