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
Text ------------------------------------------------------------------- CREATE VIEW vw_Testing_sp_helptext AS SELECT * FROM sys.databases
Ever wonder how a system stored procedure or view works? Try this:
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
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, microsoft sql server
6 thoughts on “sp_helptext”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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.
[…] 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 […]
[…] mention the probability of me missing someone. So what’s a better solution? Well if you use sp_helptext on sp_who you will see that it uses the dbid column in sysprocesses. Unfortunately here is what we […]
[…] Ctrl-T & Ctrl-D: Shift between text output and grid output. I like the grid output in general but need the text output when I’m using things like sp_helptext. […]
[…] I’ve been using sp_helptext for years. Honestly I probably end up using it at least once a day. But I was recently exposed to object_definition() which has a similar output. There are, however, a few differences. […]