CODEWITHSUNDEEP
sqlsql-serverview
Cogslave
Cogslave

Reputation: 2643

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

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?

Upvotes: 2

Views: 415

Answers (3)

Steve Ford
Steve Ford

Reputation: 7753

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

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7753

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'.

Upvotes: 1

Chamal
Chamal

Reputation: 1449

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

Upvotes: 0

Related Questions