A list of one owl's 5 favourite system stored procedures in SQL!
Part two of a six-part series of blogs

Everyone has a favourite system stored procedure (you do, don't you?). But five? That's the subject of this blog, anyway.

  1. My 5 favourite SQL system stored procedures!
  2. Listing the contents of a procedure or function (sp_helptext) (this blog)
  3. Using sp_datatype_info to get the data type of a variable
  4. Which tables/procedures depend on which? (sp_depends)
  5. Listing the columns in a table (sp_columns)
  6. 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:

Lines within procedure

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:

Lines containing commas

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 ...

  1. My 5 favourite SQL system stored procedures!
  2. Listing the contents of a procedure or function (sp_helptext) (this blog)
  3. Using sp_datatype_info to get the data type of a variable
  4. Which tables/procedures depend on which? (sp_depends)
  5. Listing the columns in a table (sp_columns)
  6. Using sp_help to get help on a table, procedure, column, etc
This blog has 0 threads Add post