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

By: Atif Shehzad   |   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 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

-- 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:

-- 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.

About the author

Atif is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

Get your priorities straight with SQL Server Monitoring

Try it live