how to find the tables in a view

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

how to find the tables in a view

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-15 : 00:19:42

Dear friends,
is there any way to find the table names from a view?

ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........

or we have to use sp_helptext?

is there any better way?

thank you very much.

Vinod

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 00:28:23

You can use

sp_depends 'Viewname'

or much better:

select * from information_schema.view_table_usage

Harsh Athalye
India.
"Nothing is Impossible"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-15 : 00:53:53

Harsha,
it is exactly working as i asked. thank you very much.

but i didnt asked the question in the rightway/sorry for that.i've a select statement around 10 pages long. i need to find all the tables belong to that query.is there any way for that? shall i post that query?

Vinod

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 03:17:16

quote:
i've a select statement around 10 pages long

You said you have a view...if it's a query, either you have to create view containing that query and use solutions given in my previous post or you will have to manually look at the tables used.

Nothing else I can think of other than that! Sorry buddy!

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-15 : 03:35:48

I guess using select * from information_schema.view_table_usage where View_Name = 'YouViewName' should be enough..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 03:39:43

quote:
I guess using select * from information_schema.view_table_usage where View_Name = 'YouViewName' should be enough..

Yes, only if he has a view

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-15 : 03:42:35

suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query?

I suspect that its the view from this statement..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 04:10:23

Yes, but mind there is big SUPPOSE and I don't presume.

Also see his second reply.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-15 : 05:28:14

quote:
Originally posted by sunsanvin

Harsha,
it is exactly working as i asked. thank you very much.

but i didnt asked the question in the rightway/sorry for that.i've a select statement around 10 pages long. i need to find all the tables belong to that query.is there any way for that? shall i post that query?

Vinod

so sp_depends doesnt give what you wanted?

Madhivanan

Failing to plan is Planning to fail

pootle_flump

1064 Posts

Posted - 2006-11-15 : 05:42:46

You could set statistics IO on and run the query in query analyser - you should receive information that (although not intended for this specific purpose) will list the tables used.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-15 : 07:18:04

quote:
Originally posted by pootle_flump

You could set statistics IO on and run the query in query analyser - you should receive information that (although not intended for this specific purpose) will list the tables used.

Yeah, plus you can tune your view depending on the physical and logical read.

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 07:20:29

What if the view is built on both tables and other views?
Is the OP asking about digging down and find all original tables?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-15 : 07:41:06

quote:
Originally posted by Peso

What if the view is built on both tables and other views?
Is the OP asking about digging down and find all original tables?

Peter Larsson
Helsingborg, Sweden

. i need to find all the tables belong to that query.

From this statement of OP, i dont think so
But atleast he should reply on it..

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources