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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

SQL Server INFORMATION_SCHEMA.VIEW_TABLE_USAGE system view
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.

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.

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.

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems