Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need a SQL query to list the table names used in a view. Expand / Collapse
Author
Message
Posted Friday, September 07, 2012 11:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:54 AM
Points: 20, Visits: 143
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
Post #1356338
Posted Saturday, September 08, 2012 12:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 4,247, Visits: 9,500
Surely the Execution Plan would be a better place to start, if performance optimisation is your goal?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1356339
Posted Saturday, September 08, 2012 5:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:54 AM
Points: 20, Visits: 143

Thanx Phil

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

Post #1356360
Posted Saturday, September 08, 2012 5:48 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,456, Visits: 14,274
can you post the view definition?

__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1356362
Posted Saturday, September 08, 2012 8:17 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:44 PM
Points: 295, Visits: 1,241
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 Maloof
Serving Data
Post #1356369
Posted Saturday, September 08, 2012 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648, Visits: 27,762
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356377
Posted Tuesday, September 11, 2012 10:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:54 AM
Points: 20, Visits: 143
Thank you very much Lowell.

This is the right query i was looking for.

Thanx Again.
Post #1357786
Posted Wednesday, September 12, 2012 1:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:54 AM
Points: 20, Visits: 143
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

Post #1357809
Posted Wednesday, September 12, 2012 5:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648, Visits: 27,762
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1357910
Posted Wednesday, September 12, 2012 6:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
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'





No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1358285
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse