How to list stored procs that use a given table

I'm sure there's an easy way to do this, if I knew my way around the data
dictionary. What is the best way to get a list of the stored procedures that
use a given table?
Also, is there a good reference for learning to use the data dictionary?
Much obliged,
Geoff.Geoff,
What you want is a list of objects (like stored procs) that are dependent on
a certain table. Don't count on SQL Server doing this for you very well. A
known "issue" with SQL Server is it's inability to maintain dependencies
accurately. Take a look at Largo SQL Tools. It does this for you without
relying on any dependency information stored by SQL Server. Take a look at
http://www.largosqltools.com/Dependency.asp.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Geoff Pennington" <penningtong@.stic2.com> wrote in message
news:OBNH2gqUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> I'm sure there's an easy way to do this, if I knew my way around the data
> dictionary. What is the best way to get a list of the stored procedures
that
> use a given table?
> Also, is there a good reference for learning to use the data dictionary?
> Much obliged,
> Geoff.
>|||Thank you. That should do very nicely.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O2qXJqqUDHA.1316@.TK2MSFTNGP12.phx.gbl...
> You can get this info with sp_depends:
> EXEC sp_depends 'MyProc'
> Note that dependency information is maintained correctly only when
> objects are created in dependency order. Consequently, the data will be
> incomplete if a dependent stored procedure was created before the table
> or if the table was recreated after the proc was created.
> You might also check out the INFORMATION_SCHEMA views. These are
> documented in the Books Online and contain a good deal of information
> about schema.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Geoff Pennington" <penningtong@.stic2.com> wrote in message
> news:OBNH2gqUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> > I'm sure there's an easy way to do this, if I knew my way around the
> data
> > dictionary. What is the best way to get a list of the stored
> procedures that
> > use a given table?
> >
> > Also, is there a good reference for learning to use the data
> dictionary?
> >
> > Much obliged,
> > Geoff.
> >
> >
>|||Or even easier:
DECLARE @.tablename SYSNAME
SET @.tablename = 'TSVFiles'
CREATE TABLE #procs
(
procname SYSNAME,
type VARCHAR(32)
)
INSERT #procs
EXEC sp_depends @.tablename
SELECT procname FROM #procs WHERE type='stored procedure'
-- if you want to include functions, leave out the where clause
DROP TABLE #procs