Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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?
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.
- 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.
Last Update: 2008-12-03
About the author
View all my tips