When sp_HelpText is not so helpful!

,

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.

Rate

4.89 (9)

Share

Share

Rate

4.89 (9)