sp_helptext Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_helptext Query

Author

Topic

amitmnagarwal
Starting Member

15 Posts

Posted - 2008-12-01 : 08:07:41

when we do a sp_helptext of a stored procedure in Text Mode,

like this sp_helptext <sp_name>

we also get keyword called Text, as highlighted below,

Text---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE Procedure [prcMaintenance_Sel]
@nProjectid int,
@nUserid int,
--'Below line changed as per IAS-EPM-06-11-07(RoleBasedAccess-Maintenance)
@bitIsAllRights bit,
@brevcom bit,
@sDisplay varchar(250) OUT,

@PageNumber int,
@PageSize int,
@sortField varchar(320),
@totalrecords int output

Is there a way we can avoid the keyword "Text" which the sp_helptext generates, as we have to manually delete each time the 'Text'

Kindly let me know if this is possible.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 08:31:39

What is the reason to manually remove the "Text" record header?

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-01 : 08:38:47

You can do this to get all the text of your object into a variable. Then do what you want with the variable. Don't know if this is what you are after though....


DECLARE @object NVARCHAR(MAX)

SELECT @object = 'your procedure / function here'

DECLARE @text NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#sqlText') IS NOT NULL DROP TABLE #sqlText

CREATE TABLE #sqlText (
	[Id] INT IDENTITY(1,1)
	, [line] NVARCHAR(255)
	)

INSERT #sqlText EXEC sp_helpText @object
SELECT @text = @text + [Line] FROM #sqlText ORDER BY [Id] ASC
SELECT @text

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 08:45:17

[code]
DECLARE @text NVARCHAR(MAX)

SELECT @text=COALESCE(@text,'') + text
FROM sys.syscomments
WHERE OBJECT_NAME(id)='Your Procedure name'

SELECT @text
[/code]

amitmnagarwal
Starting Member

15 Posts

Posted - 2008-12-02 : 08:08:42

I need this when we generate big scripts involving atleast 30 sps ,

then we have to manuaally search for the keyword 'Text' and then delete it,

so queried, is there a way to avoid the Text being generated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 09:12:19

quote:
Originally posted by amitmnagarwal

I need this when we generate big scripts involving atleast 30 sps ,

then we have to manuaally search for the keyword 'Text' and then delete it,

so queried, is there a way to avoid the Text being generated

did you try my suggestion?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-02 : 09:17:17

I think you need this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:00:01

Or this http://weblogs.sqlteam.com/peterl/archive/2008/10/24/How-to-script-out-all-your-objects-one-per-file.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources