Skip to content

Instantly share code, notes, and snippets.

@gelicia gelicia/gist:3888251

Created Oct 14, 2012
Embed
What would you like to do?
Find original columns for views TSQL SQL Server
WITH view_info (origViewName, viewName, tableName, columnName, viewPath, nestedViewDepth)
AS (
SELECT vCol.view_name, vCol.view_name, vCol.table_name, vCol.column_name,
CAST(vCol.view_name + ',' + vCol.table_name AS VARCHAR(MAX)), 1
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE vCol
UNION ALL
SELECT vi.origViewName, vColChild.view_name, vColChild.table_name, vColChild.column_name,
CAST(vi.viewPath + ',' + vColChild.table_name AS VARCHAR(MAX)),
vi.nestedViewDepth + 1
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE vColChild
INNER JOIN view_info vi ON vi.tableName = vColChild.view_name AND vi.columnName = vColChild.COLUMN_NAME
)
SELECT vInf.*
FROM view_info vInf
LEFT OUTER JOIN INFORMATION_SCHEMA.views v ON vInf.tableName = v.table_name
WHERE v.table_name IS NULL
ORDER BY origViewName, tableName, columnName
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.