View - Table Usage (Recursive)
Wrote this to analyze the tables that are contained in a view. The problem I had with the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE view is that if one view queries another it reports the view as the Base Table, not the actual database table the data comes from. Considering that this is a legal column definition in a view "1 As Id" then we can see why INFORMATION_SCHEMA doesn't attempt to dig down and tell us where that data came from, since the correct answer could be NULL.
But there are so many times when are views are 'honest' queries between multiple related tables and the occasional view. So if that's what you have this may be a useful way for you to uncover the tables that made up your view, without individually looking at the dependencies visualizer for each view or spending $$$ on software to do that for us. This query attempts to assist in that situation. I say attempts because it is not heavily tested and has limitations as I've hinted at above.
Also includes two other queries that show different summaries of the same data. Intended as an analysis query not something to use in a prodcution app. Requires SQL 2005 or higher. Written on SQL 2005.
I imagine community members will be able to point out additional issues or enhancements. I welcome your feedback.
/*
Underlying tables in a view
Author: Rhett Clement
Description: Shows the tables that a view consists of, useful if you have views that query
other views
Notes: Sorry about the formatting
*/DECLARE @OriginalObject varchar(128)
SET @OriginalObject = 'MyViewNameHere';
WITH CTE(Original, ViewName, TableName, Depth, column_name)
AS
(
-- The first select grabs the top level, the original view and its columns
-- we initialize depth to 0
SELECT VIEW_NAME as Original, VIEW_NAME
,TABLE_NAME, 0 as Depth, column_name
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME = @OriginalObject or ISNULL(@OriginalObject, '') = ''
UNION ALL
-- Now the recursive select joins to the previous results and increments depth
-- We join the name of the view to the source of the columns from the previous depth level
-- so as long as we have a view providing columns at a given depth we will recurse until
-- we find only tables
SELECT c.Original , VIEW_NAME, TABLE_NAME, c.Depth + 1, meta.column_name
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE As meta
INNER JOIN CTE c
ON meta.VIEW_NAME = c.TableName
)
SELECT *
INTO #Cte
FROM Cte
/* Report on the results
- Original: The original view we analyzed
- ViewName: The view that was queried at the given depth level (will be equal to Original at depth of 0)
- Source: The source of the columns for the view (one entry for each source)
- SourceType: The nature of the source of the columns TABLE/VIEW (if it is a view then expect a subsequent depth level)
- Depth: The depth level
- ColumnsFromSource: The number of columns that came from that source (may be under-reporting)
- ColumnsAtDepth: The number of columns total for the given depth level
*/
SELECT Original, ViewName, TableName as Source
,CASE (select COUNT(*) From INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TableName = TABLE_NAME)
WHEN 1 THEN 'TABLE'
ELSE 'VIEW' END AS SourceType
,Depth
,Count(distinct column_name) as ColumnsFromSource
,(SELECT COUNT(distinct column_name)
FROM #Cte as innerCTE
WHERE innerCTE.depth = CTE.Depth and innerCTE.Original = CTE.Original) as ColumnsAtDepth
FROM #Cte as CTE
GROUP BY Original, ViewName, TableName, Depth
-- This order focuses on the composition of original/primary view
ORDER BY Original, Depth , SourceType DESC
-- Just the tables from the view
SELECT Original, TableName as Source
FROM #Cte
WHERE EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TableName = TABLE_NAME)
GROUP BY Original, TableName
ORDER BY Original, TableName
-- Table counts instead
SELECT Original, Count(Distinct TableName) as TableCount
FROM #Cte
WHERE EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TableName = TABLE_NAME)
GROUP BY Original
ORDER BY TableCount Desc
DROP TABLE #Cte
