SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

When sp_HelpText is not so helpful!

By Andy Robertson,

I guess we've all scripted objects out of our database one way or another. We've scripted stuff out of SSMS or using sp_helptext, Object_definition or a tool like Redgate's SQL Prompt - my go to tool for this sort of thing. However, if you're using sp_helptext then you should be aware of the limit of 255 characters on each row as it could have some very interesting effects on your scripts.

This is the link to Microsoft's help page (at the moment) for sp_helptext: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helptext-transact-sql

If you create the following test procedure in SSMS you can see that it has a long dummy comment that for example contain an explanation as to why this line of code was changed (the reason a WHERE clause was removed for example)!

CREATE PROCEDURE MyTestProc
AS
BEGIN

SELECT * FROM SomeTable -- text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text some text -- WHERE SomeTable.Column = 1

END
GO

Now run sp_helptext for this procedure

sp_helptext MyTestProc

The resulting script looks like this (yes i've shortened the text/comment bit for effect). Essentially you've magically added back in a where clause to your procedure. Eeek!!!

CREATE PROCEDURE MyTestProc  
AS  
BEGIN  
  
SELECT * FROM SomeTable -- text some text some text ... text shortened...some text -- 
WHERE SomeTable.Column = 1  
  
END  

Conclusion

Be very careful when scripting using sp_helptext as it may shift a fully function sql statement on to a new line that completely changes your object. Granted you have to be very unlucky, but it is definitely possible! Most times of course you will find that your new script doesn't parse and won't run. Of the pros and cons of different scripting techniques this is the worst con I've found so far. I think the fact that it is a microsoft system stored procedure means that some users will think the script is guaranteed to be what is actually running on the server.

 
Total article views: 1127 | Views in the last 30 days: 1127
 
Related Articles
FORUM

Disabling SP_Helptext

How to disable SP_Helptext

SCRIPT

Generate Drop / Create Script for views, stored Procedures

This Script generates drop and create scripts for views or stored procedures.

FORUM

create Procedure within Procedure

create Procedure within Procedure

ARTICLE

A Syntax Mystery in a Previously Working Procedure

A previous working script show syntax errors on recreating (sp_helptext script line length limitatio...

FORUM

SP_HELPTEXT

SP_HELPTEXT

Tags
sp_helptext    
t-sql    
 
Contribute