Skip to content

Instantly share code, notes, and snippets.

@gelicia
Created October 14, 2012 10:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gelicia/3888251 to your computer and use it in GitHub Desktop.
Save gelicia/3888251 to your computer and use it in GitHub Desktop.
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