SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Identifying the Tables used in procedure or function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/22/2006 :  10:15:56  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
MAH writes "Hi,
How to identify the all tables in stored procedure and function"

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/22/2006 :  10:49:45  Show Profile  Reply with Quote
exec sp_depends 'object name'
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/22/2006 :  12:31:36  Show Profile  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/22/2006 :  12:39:01  Show Profile  Reply with Quote
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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/22/2006 :  12:47:49  Show Profile  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/22/2006 :  13:01:26  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000