how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
Vivek T S
Member Technical Staff (Inucom)
try sp_depends viewname
HTH
"Vivek T S" wrote:

> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
David G.
|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||David G. wrote:
> Vivek T S wrote:
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
David G.
|||Thanks guys.
One more question. Can i do the same using say an ODBC program (which is my
real interest)
"Kalen Delaney" wrote:

> exec sp_helptext empMaster
> This will show the TSQL definition used to define the view.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
>
>