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 four 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) (this blog) 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 four 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.

Posted by Andy Brown on 03 February 2017

In this blog

Which tables/procedures depend on which? (sp_depends)

This is so useful that I wish I'd known it before. You can use it in one of two main ways.

Suppose you have a table called Film, and you want to see what views, procedures, etc refer to it. Here's how to do this:

-- what uses the films table

sp_depends 'Film'

It turns out there are two procedures and a view referencing this table:

The list of objects which depend on the specified table.

Turning the question round, here's how to show which tables and columns a particular stored procedure references:

-- what does the spExample procedure use

sp_depends 'spExample'

Here's the answer for this procedure:

The three columns referenced by this procedure.

It even tells you if the procedure makes changes to a column!

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) (this blog) Listing the columns in a table (sp_columns) Using sp_help to get help on a table, procedure, column, etc