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.