Learn more about SQL Server tools

   
   















































Using SQL Server meta data to list tables that make up views

MSSQLTips author Atif Shehzad By:   |   Read Comments (5)   |   Related Tips: More > Database Administration

Problem
One of the issues I often face is the need to find views that are already established for certain tables. This maybe for other developers, end users or even for myself.  I could search the system tables to find this or explore each view, but are there other ways to easily find a list of all tables that are used for a view or even a list of all views that a table is tied to?

Solution
Generally we can access SQL Server meta data either by using a system stored procedure or through INFORMATION_SCHEMA views. System stored procedure are excellent and optimized for a DBA when using through a query window, but these are not always suitable for application users, because you may be required to further filter the information and thus hinder the performance. So, I decided to get the required information from the INFORMATION_SCHEMA views of SQL Server.

In the below example I retrieve the base table information for view 'vEmployee' in the 'AdventureWorks' database.

Syntax and example to get base tables for a view
-- Syntax to get base tables for a view
USE DBName
GO SELECT view_name, Table_Name FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE View_Name = '<giveViewName>' ORDER BY view_name, table_name GO
Syntax to get data for view 'vEmployee'
-- Get base tables for 'vEmployee' in AdventureWorks 
USE AdventureWorks 
GO
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = 'vEmployee'
ORDER BY view_name, table_name
GO

The result for the above query shown below and we can now see all of the tables that are part of this view.

If we wanted to look at this from a different angle and find all views that a table is part of we can use the following command:

Syntax to get data for table 'Address'
-- Get views for table 'Address' in AdventureWorks 
USE AdventureWorks 
GO
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE Table_Name= 'Address'
ORDER BY view_name, table_name
GO

The result for the above query shown below and we can now see all views that the Address table is part of.

Meta data views are defined in the INFORMATION_SCHEMA. These views can be found in SQL Server 2000 and newer versions of SQL Server. The INFORMATION_SCHEMA schema is provided in each database of SQL Server. In SSMS you can find views for the 'INFORMATION_SCHEMA' schema in the 'System Views' folder under 'Views' as shown below.

Next Steps

  • Although this is a pretty simple tip, hopefully it will save you some time next time you are looking for which tables make up a certain view.
  • In addition to the above columns that we used, the INFORMATION_SCHEMA.VIEW_TABLE_USAGE view includes the following additional data elements: (VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME), so take a look to see how you can use this information.
  • Take the time to explore the other INFORMATION_SCHEMA views to find out what other information is easily exposed for your use.


Last Update: 12/3/2008


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, June 05, 2012 - 12:57:36 AM - Atif Read The Tip

@sai. For this scenaio you may union the following statement

SELECT view_name, Table_Name
FROM [LinkedServer].[LinkedDB].INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name in ('View1','View2')

I hope it would work. If there is any issue then please mention here.

 

 


Thursday, May 31, 2012 - 10:15:56 AM - sai Read The Tip

 

This above code does not list if the view is created from the tables of  a linked server. can you please let me know if you know any way to do this? Thanks


Monday, May 14, 2012 - 12:15:51 AM - Azhar Iqbal Read The Tip

Nice Information.


Monday, April 16, 2012 - 6:38:10 PM - MemphisMaven Read The Tip

Thank you so much.  Very helpful!!!


Thursday, December 04, 2008 - 11:56:12 AM - jerryhung Read The Tip

 Thank you

 easier than select a view in SSMS and View Dependecies, and find the Tables manually




 
Sponsor Information