Tables Involved in a Query

Question

Post reply

Tables Involved in a Query

ganeshmuthuvelu

SSCrazy

Points: 2177
More actions
March 5, 2005 at 10:21 am

#162882

Hi,
I have a peculiar need. I want a SP or function which takes a SQL statement like 'select * from orders' or something like 'exec get_orders' as INPUT parameter and then return me the list of tables involved inb the query..
For example, if send this statement below as inout parameter,
select * form orders inner join customers on orders.customer_no=customers.customer_no
the function should return me this result:
1) orders
2) customers
which means the "orders" table and "customers" table are involved in the query.
Is this something possible?. Experts, I need your advise.
Thanks,
Ganesh

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Yoav S. SSC Eights! Points: 924 More actions · Answer 1

Hi,

Do you mean you want to see all the results from the 2 table together?

in this case you can use cross join. from BOL (using cross joins) :

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.

yoav.

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 2

Hi,

Thanks, but that is not what I want. I want to know just the "NAME" of the "tables" involved in the query not the joined results or the data contained in the tables.

wz700 SSCertifiable Points: 7133 More actions · Answer 3

check involved table in stored procedure, sp_depends can help you.

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 4

wz700,

Thanks for the reply. Yes, "sp_depends" did help me for the Stored Procedure and other objects in the database.

It helped me for one half of the problem. Can you please point me how I can get a similar result returned by "sp_depends" for my "queries" also?. For example, if I submit a query like "select * from orders", I should get the dependencies as "orders".

Thanks for your time.

Ganesh