|
|
|
Grasshopper
      
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
|
|
|
|
|
SSCarpal 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.
|
|
|
|
|
Grasshopper
      
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.
|
|
|
|
|
UDP 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 ! __________________________________________________________________
|
|
|
|
|
SSC 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
|
|
|
|
|
SSChampion
        
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
|
|
|
|
|
Grasshopper
      
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.
|
|
|
|
|
Grasshopper
      
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
|
|
|
|
|
SSChampion
        
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346,
Visits: 3,192
|
|
|
|
|