This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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

-- 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! For how to write your own stored procedures, consider booking a place on our Advanced SQL course.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Listing the contents of a procedure or function (sp_helptext)

Head office

London

Manchester