Need a SQL query to list the table names used in a view.

Question

Need a SQL query to list the table names used in a view.

neellotus07

Ten Centuries

Points: 1245
More actions
September 7, 2012 at 11:40 pm

#263944

Dear All,
I have a view with 7 to 9 tables and it is running slow so i need a query to list all the table names used in a view.
the query should populate as:-
view Table
____ _____
view1 tab1
view1 tab1
Can anyone help me?
Thanx
Neel

Viewing 11 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Surely the Execution Plan would be a better place to start, if performance optimisation is your goal?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

neellotus07 Ten Centuries Points: 1245 More actions · Answer 2

Thanx Phil

But i need a query to find out tables which are used by view.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

can you post the view definition?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Peter Maloof SSCommitted Points: 1733 More actions · Answer 4

If it's just one view, you could script it out.

Otherwise, this seems to work in SQL Server 2012:

use AdventureWorks2012;

select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS

where TABLE_NAME='vSalesPerson';

Peter MaloofServing Data

Lowell SSC Guru Points: 323518 More actions · Answer 5

including the columns referenced:

select

OBJECT_NAME(depz.object_id),

OBJECT_NAME(depz.referenced_major_id),

colz.name,

*

from sys.sql_dependencies depz

left outer join sys.columns colz

on depz.object_id = colz.object_id

and depz.referenced_minor_id=colz.column_id

where OBJECT_NAME(depz.object_id) = 'YourViewName'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

neellotus07 Ten Centuries Points: 1245 More actions · Answer 6

Thank you very much Lowell.

This is the right query i was looking for.

Thanx Again.

neellotus07 Ten Centuries Points: 1245 More actions · Answer 7

Hi Lowell,

Your query showing tables of any view, but if view contains another view then it is not showing name of that view.

Can u help me in this regard.

Thanx

Neel

Lowell SSC Guru Points: 323518 More actions · Answer 8

neellotus07 (9/12/2012)
Hi Lowell,
Your query showing tables of any view, but if view contains another view then it is not showing name of that view.
Can u help me in this regard.
Thanx
Neel

Are you sure? when i just tested a view which i KNOW contains a mix of other views and tables, i get the list of exactly what is referenced int he view; i would need to "drill down", so to speak , to get the tables that teh internal views hit; is that what you are asking? the way you framed the question makes me think the results don't show the views at all.

can you clarify the issue?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

Another way:

-- To get just the table names

SELECT DISTINCT TABLE_NAME

FROM information_schema.view_column_usage

WHERE VIEW_NAME = 'v_YourView'

-- To get table and column names

SELECT TABLE_NAME, COLUMN_NAME

FROM information_schema.view_column_usage

WHERE VIEW_NAME = 'v_YourView'

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

neellotus07 (9/7/2012)
Dear All,
I have a view with 7 to 9 tables and it is running slow so i need a query to list all the table names used in a view.
the query should populate as:-
view Table
____ _____
view1 tab1
view1 tab1
Can anyone help me?
Thanx
Neel

Gosh... What a strange question. I know you have the answer but if you have a view that is running slow, just listing the names of the tables in the view code isn't going to do much to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)