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

By: Atif Shehzad   |   Updated: 2008-12-03   |   Comments (9)   |   Related: 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.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips