Is there a way to find all invalid columns that are referenced in a view using SQL Server 2012?

Question

I have inherited a large database project with thousands of views.

Many of the views are invalid. They reference columns that no longer exist. Some of the views are very complex and reference many columns.

Is there an easy way to track down all the incorrect columns references?

You could adapt example B from the [`sp_refreshview`](http://technet.microsoft.com/en-us/library/ms187821.aspx) page (by removing the join) to create a script that attempts to refresh every view in the database. Then run that script and it should produce an error for every view that can no longer work — Damien_The_Unbeliever, Sep 23 '13 at 07:55

Steve Ford · Answer 1 · 2013-10-17T10:20:59.333

UPDATED TO RETRIEVE ERROR DETAILS

So this answer gets you what you want but it isn't the greatest code.

A cursor is used (yes I know :)) to execute a SELECT from each view in a TRY block to find ones that fail. Note I wrap each statement with a SELECT * INTO #temp FROM view X WHERE 1 = 0 this is to stop the EXEC returning any results and the 1=0 is so that SQL Server can optimize the query so that it is in effect a NO-OP.

I then return a list of any views whose sql has failed.

I haven't performed lots of testing on this, but it appears to work. I would like to get rid of the execution of each SELECT from View.

So here it is:

DECLARE curView CURSOR FOR
    SELECT  v.name AS ViewName
    FROM sys.views v
    INNER JOIN sys.sql_modules m
        on v.object_id = m.object_id

OPEN curView
DECLARE @viewName SYSNAME

DECLARE @failedViews TABLE
(
    FailedViewName SYSNAME,
    ErrorMessage VARCHAR(MAX)
)

FETCH NEXT FROM curView 
    INTO @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        exec ('SELECT * INTO #temp FROM ' + @viewName + ' WHERE 1=0' )
    END TRY
    BEGIN CATCH
        INSERT INTO @failedViews VALUES (@viewName, ERROR_MESSAGE())
    END CATCH
    FETCH NEXT FROM curView 
        INTO @ViewName
END
CLOSE curView
DEALLOCATE curView

SELECT *
FROM @failedViews

An example of an ERROR returned is:

FailedViewName  ErrorMessage
--------------- -------------
vwtest          Invalid column name 'column1'.

This only displays the view name? I wanted to find the column that was causing the view to fail. Some of the tables have a lot of columns, so doing it manually is slow. — Cogslave, Oct 15 '13 at 11:08
@Alex I've updated answer to report the view error message which will give details of the column errors — Steve Ford, Oct 17 '13 at 10:21
@Alex thanks for accepting, but I think my other answer might help you more — Steve Ford, Oct 17 '13 at 11:15

score 1 · Accepted Answer · edited May 23 '17 at 12:21

This answer finds the underlying columns that were originally defined in the views by looking at sys.views, sys.columns and sys.depends (to get the underlying column if the column has been aliased). It then compares this with the data held in INFORMATION_Schema.VIEW_COLUMN_USAGE which appears to have the current column usage.

SELECT  SCHEMA_NAME(v.schema_id) AS SchemaName, 
        OBJECT_NAME(v.object_id) AS ViewName, 
        COALESCE(alias.name, C.name) As MissingUnderlyingColumnName
FROM sys.views v
INNER JOIN sys.columns C
    ON C.object_id = v.object_id
LEFT JOIN sys.sql_dependencies d 
    ON d.object_id = v.object_id
LEFT JOIN sys.columns alias
    ON d.referenced_major_id = alias.object_id AND c.column_id= alias.column_id
WHERE NOT EXISTS
        (
            SELECT * FROM Information_Schema.VIEW_COLUMN_USAGE  VC
            WHERE VIEW_NAME = OBJECT_NAME(v.object_id) 
                AND VC.COLUMN_NAME = COALESCE(alias.name, C.name)
                AND VC.TABLE_SCHEMA = SCHEMA_NAME(v.schema_id)
        )

For the following view:

create table test
( column1 varchar(20), column2 varchar(30))


create view vwtest as select column1, column2 as column3 from test


alter table test drop column column1

The query returns:

SchemaName  ViewName    MissingUnderlyingColumnName
dbo         vwtest      column1

This was developed with the help of this Answer

Thanks, this answer is better. It runs much faster. – Cogslave Oct 17 '13 at 11:30 — Cogslave, Oct 17 '13 at 11:30
@Alex that's one of the benefits of not using cursors! – Steve Ford Oct 17 '13 at 11:38 — Steve Ford, Oct 17 '13 at 11:38

score 0 · Answer 3 · answered Sep 23 '13 at 09:53

0

You could use system tables get information.

SELECT v.VIEW_NAME,v.TABLE_CATALOG,v.TABLE_SCHEMA,v.TABLE_NAME,v.COLUMN_NAME
from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE v
left outer join INFORMATION_SCHEMA.COLUMNS c
ON v.TABLE_CATALOG=c.TABLE_CATALOG AND v.TABLE_SCHEMA=c.TABLE_SCHEMA AND v.TABLE_NAME=c.TABLE_NAME AND v.COLUMN_NAME=c.COLUMN_NAME
WHERE c.TABLE_NAME IS NULL
ORDER BY v.VIEW_NAME

answered Sep 23 '13 at 09:53

Chamal

1,439
10
15

2

This only returns columns that still exist in the tables, not columns that are reference in the view which no longer exist in the tables. Try it CREATE TABLE test1 (ID INT,NAME VARCHAR(50), Additional VARCHAR(50)) GO CREATE VIEW vTest1 AS SELECT Id, Name, Additional FROM test1 GO ALTER table Test1 DROP COLUMN Additional GO SELECT * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE v WHERE VIEW_NAME = 'vtest1' – Steve Ford Sep 23 '13 at 10:14

Is there a way to find all invalid columns that are referenced in a view using SQL Server 2012?

3 Answers3