sp_helptext

3

September 2, 2012 by Kenneth Fisher

My manager has been quietly laughing under his breath at me for years. Usually because I much prefer queries and system tables/functions to using the GUI. I’ll look for the table I want in sys.tables, stored procedures in sys.procedures etc. This preference is probably why, when I found this little gem a number of years ago I instantly fell in love. (It’s not creepy that I fell in love with a system stored procedure is it?)

sp_helptext can be passed any SQL Server object that contains code, for example stored procedures, views, functions, triggers etc. It then returns the code for that object.

If I have a view

CREATE VIEW vw_Testing_sp_helptext AS
SELECT * FROM sys.databases

and run

sp_helptext vw_Testing_sp_helptext

it returns.

Text
-------------------------------------------------------------------
CREATE VIEW vw_Testing_sp_helptext AS
SELECT * FROM sys.databases

Ever wonder how a system stored procedure or view works? Try this:

sp_helptext [sys.databases]

or even

sp_helptext sp_helptext

A few notes:

If you have to use a 2, 3 or 4 part name then you have to put []s around the name.

I would suggest using “Results to Text” instead of “Results to Grid” for the output. “Results to Text” will keep any formating you have while “Results to Grid” appears to convert tabs to a single space.

This otherwise wonderful system stored procedure does have one minor flaw. If a line in the stored procedure is long enough then the output breaks it. Not usually a big issue but here is an example:

CREATE PROCEDURE usp_Testing_sp_helptext AS
SELECT 'This string represents a really long line in a query in order to demonstrate a minor problem with sp_helptext. The line has to be really really long though in order to create a problem. In fact I think the line has to be longer than 255 characters in order to show the problem. I have to admin though it is a pain in to create a string long enough.'
FROM sys.databases
sp_helptext usp_Testing_sp_helptext
Text
-------------------------------------------------------------------
CREATE PROCEDURE usp_Testing_sp_helptext AS
SELECT 'This string represents a really long line in a query in order to demonstrate a minor problem with sp_helptext. The line has to be really really long though in order to create a problem. In fact I think the line has to be longer than 8000 charact
ers in order to show the problem. I have to admin though it is a pain in to create a string long enough.'
FROM sys.databases

If you look at the devision between lines 4 and 5 you will see that the word “characters” is broken in half. Not really a big deal as the code will almost always still continue to work and it doesn’t happen all that frequently but still something to keep an eye on.

About these ads

3 thoughts on “sp_helptext

  1. geraldine says:

    It is an issue. When I try to recreate the procedure from the file with the broken line it fails with syntax errors.

    • If I understand correctly you are seeing broken lines in the output of sp_helptext and when you try to run the code it gives errors. This is because the output (per line) from sp_helptext is only 4000 characters. So if you have a long line of code it will get broken at that point. You can either go through and fix the code each time you pull it from sp_helptext or shorten the lines of code and you shouldn’t have a problem anymore.

  2. […] however I want. However I want to know how exactly it’s getting the data. So I decided to use sp_helptext to get the code behind this stored […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 487 other followers

%d bloggers like this: