Q:

How to find all views with invalid column names

I am trying to refresh all my views using code I found on the web:
DECLARE @ViewName VARCHAR(256)
DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views
OPEN cViews
FETCH NEXT FROM cViews INTO @ViewName
WHILE @@FETCH_STATUS != -1
BEGIN
    EXEC SP_REFRESHVIEW @ViewName
    PRINT 'View ''' + @ViewName + ''' has been refreshed.'
    FETCH NEXT FROM cViews INTO @ViewName
END
CLOSE cViews
DEALLOCATE cViews

I am getting the error Error Message: Invalid column name 'CloseDate'., but it's not telling me which view has the error. How would I find the name of all views with an invalid column name with the name of the invalid column? Thank you.

A:

You could try importing the database into an SSDT database project and building the project. It should report this type of thing.
Alternatively you could try
SELECT *
FROM   sys.views
       CROSS APPLY sys.dm_sql_referenced_entities (
                                        CONCAT(QUOTENAME(SCHEMA_NAME(SCHEMA_ID)), '.', QUOTENAME(NAME)), 
                                        'OBJECT') 

And then peruse the error output

