Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (6)   |   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:





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





More SQL Server Solutions




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     



Wednesday, December 02, 2015 - 3:28:38 PM - Yogesh Back To Top

This is exactly what I was looking for. Very nice article


Tuesday, June 05, 2012 - 12:57:36 AM - Atif Back To Top

@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 Back To Top

 

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 Back To Top

Nice Information.


Monday, April 16, 2012 - 6:38:10 PM - MemphisMaven Back To Top

Thank you so much.  Very helpful!!!


Thursday, December 04, 2008 - 11:56:12 AM - jerryhung Back To Top

 Thank you

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


Learn more about SQL Server tools