COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
A list of one owl's 5 favourite system stored procedures in SQL!
Part six 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)
  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)

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.

Using sp_help to get help on a table, procedure, column, etc

If you only remember one system stored procedure, this is probably the one to choose.  Here are some examples of its use.

Getting information about a table

To get information about a specific table, use its name as your parameter:

-- get information on the table called DIRECTOR

sp_help 'Director'

Here's what this would show:

Table info

Lots of useful information here!

Getting the parameters required by a procedure

If you want to use a system (or your own) stored procedure, but can't quite remember what parameters it takes, try this:

-- what parameters does a procedure take?

sp_help 'sp_who'

This shows that this particular system stored procedure (sp_who) takes one parameter:

sp_who parameter

You must supply a single string to the procedure.

Getting a list of everything in your database

Use sp_help without any parameters to get a listing of everything in your database:

-- get a list of everything!

sp_help

Here are the first few of the 2,283 rows returned for the Wise Owl Movies database:

sp_help output

The first few rows returned, out of many!

 

And that's the end of this blog! 

You can learn how to write stored procedures by attending one of our advanced SQL courses (we now have a live online two-day advanced SQL course which anyone can attend from anywhere in the world).

 

This blog has 0 threads Add post