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 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. My 5 favourite SQL system stored procedures! Listing the contents of a procedure or function (sp_helptext) 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 (this blog)

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.

My 5 favourite SQL system stored procedures!
Listing the contents of a procedure or function (sp_helptext)
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 (this blog)

Posted by Andy Brown on 03 February 2017

In this blog

Getting information about a table
Getting the parameters required by a procedure
Getting a list of everything in your database

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:

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:

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:

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 (you can attend the online version of our two-day advanced SQL course from anywhere in the world).

Parts of this blog
My 5 favourite SQL system stored procedures! Listing the contents of a procedure or function (sp_helptext) 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 (this blog)