BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Everyone has a favourite system stored procedure (you do, don't you?). But five? That's the subject of this blog, anyway.
- My 5 favourite SQL system stored procedures!
- Listing the contents of a procedure or function (sp_helptext) (this blog)
- Using sp_datatype_info to get the data type of a variable
- Which tables/procedures depend on which? (sp_depends)
- Listing the columns in a table (sp_columns)
- Using sp_help to get help on a table, procedure, column, etc
Posted by Andy Brown on 03 February 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Listing the contents of a procedure or function (sp_helptext)
This has to be the most fun system stored procedure, although maybe I just think so because I don't get out enough. Let's suppose you've created a stored procedure:
CREATE PROC spExample
AS
-- list out all films
SELECT
f.Title
, f.OscarWins AS Oscars
, f.RunTimeMinutes AS RunTime
FROM
Film AS f
ORDER BY
f.Title
You now want to find all the lines containing commas. So you use sp_helptext:
-- analyse this procedure
sp_helptext 'spExample'
The results are a bit weird:
The lines which comprise your stored procedure!
You could put the results in a temporary table, and use this to show all the lines containing commas:
DECLARE @lines TABLE(
LineText VARCHAR(MAX)
)
-- put lines of procedure into table
INSERT INTO @lines
EXEC sp_helptext 'spExample'
-- now show lines beginning with commas
SELECT
LineText
FROM
@lines
WHERE
LineText LIKE '%,%'
The results are impressive:
The two lines containing commas.
I'm not QUITE sure how you'd use this information, but sometimes the fun is in the answer, not how it's used ...
- My 5 favourite SQL system stored procedures!
- Listing the contents of a procedure or function (sp_helptext) (this blog)
- Using sp_datatype_info to get the data type of a variable
- Which tables/procedures depend on which? (sp_depends)
- Listing the columns in a table (sp_columns)
- Using sp_help to get help on a table, procedure, column, etc