Find SQL Server Views Where Table is used and List Tables in a View
To query database objects metadata in which SQL views a table is used or which tables are used in a SQL Server database view SQL Server database administrator and developers can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE system view. SQL Server database management system views help administrators and programmers query database metadata and find useful information to ease daily tasks, etc.
SQL Server database administrator and developer can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE system view to query database objects metadata to find in which SQL views a table is used or which tables are used in a SQL Server database view.
SQL Server View and Table Usage
SQL Server provides database administrators and SQL developers system views which help them to query metadata about database objects. INFORMATION_SCHEMA.VIEW_TABLE_USAGE is one of the SQL Server system views which shows the relation between SQL views and database tables.
SQL Server INFORMATION_SCHEMA.VIEW_TABLE_USAGE System View
As you can understand from the list of SQL view's column names, this is a mapping table where database views as view_name and table names as table_name can be listed by selecting data from this management view.
Let's now query INFORMATION_SCHEMA.VIEW_TABLE_USAGE view to see what result set it will return on AdventureWorks2014 sample database for example.
use AdventureWorks2014
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
The first 3 columns of the result set contain database name, schema name, and the view name. Following 3 columns contain fully qualified name of the SQL database tables where data is read from.
INFORMATION_SCHEMA.VIEW_TABLE_USAGE resultset on a sample database
List Database Tables used in a SQL View
SQL developers can modify the above SELECT statement by adding view name in WHERE clause to list database tables used in a specific SQL view.
select *
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where
-- VIEW_CATALOG = 'AdventureWorks2014' and
-- VIEW_SCHEMA = 'Sales' and
VIEW_NAME = 'vIndividualCustomer'
As seen in below screenshot, when SQL developers apply a filter on View_Name column of View_Table_Usage information schema view, the tables used in that specific view are listed as the resultset. Administrators and programmers can use this query before making a change on the view for estimating the database tables that will be affected.
List of database tables used in a SQL view
Search in SQL Server Views where a Database Table is used
Of course by changing this approach, SQL developer or database administrator can query INFORMATION_SCHEMA.VIEW_TABLE_USAGE system view to list database views where a specific table is used. To find all of the SQL Server database views where a table is used, just apply a filter criteria on table_name column of the information schema view INFORMATION_SCHEMA.VIEW_TABLE_USAGE as seen below.
select
VIEW_CATALOG,
VIEW_SCHEMA,
VIEW_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where
TABLE_CATALOG = 'AdventureWorks2014' and
TABLE_SCHEMA = 'Person' and
TABLE_NAME = 'Person'
Here is the list of SQL views where the database table Person is used.
Find SQL Views where a table is used
To summarize, INFORMATION_SCHEMA.VIEW_TABLE_USAGE information schema view returns one record for each table used in a view checking if the query running user has permissions on the objects. If the current user has required permissions on the view and tables used inside, the user can execute a SQL query like shared above to find tables used in a view or to find view names where a table is used.
Related SQL Server tutorial and articles for database administrator and T-SQL developers:
SQL Object Where Used List by a Search SQL Database Objects Text
How to Search SQL Text Anywhere in SQL Server Database