SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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 database management 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

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

SQL Server database management view
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.

 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.

query SQL Server metadata for view tables
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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.