Identifying the Tables used in procedure or function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identifying the Tables used in procedure or function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-22 : 10:15:56

MAH writes "Hi,
How to identify the all tables in stored procedure and function"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 10:49:45

exec sp_depends 'object name'

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-22 : 12:31:36

quote:
Originally posted by snSQL

exec sp_depends 'object name'

Well that sounds like the opposite of what they want.

Put Query Analyzer into Grid mode and then excute the following


SELECT o.name, t.TABLE_NAME, c.text 
  FROM syscomments c 
  JOIN sysobjects o 
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%'+t.TABLE_NAME+'%'

Now if you've got some funky able names like [a]. [n], like I do, you get a lot tof hits that aren't real.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 12:39:01

quote:
Well that sounds like the opposite of what they want.

Maybe I wasn't clear, in the statement

exec sp_depends 'object name'

the object name will refer to the stored proc or function. Then you'll get back a list of the tables used by the proc or function, which I believe is exactly what MAH wants?

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-22 : 12:47:49

Yeah, but you'd have to do it for every sproc and/or function in the database...

Did you try my code?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 13:01:26

Sure, it depends on what you're asking for. If you want everything in one go, then yes, querying the system tables is the way to go. I interpreted the question to be asking how to do it for one proc or function, and I have no idea if that was right or wrong

- looking at it again it does say "stored procedure and function" so you're probably right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources