T-SQL: Check for invalid views - remember to refresh!

Recently on the MSDN forums, I came across a post which wanted to find orphaned views - those which have had their base tables changed/removed so that they are no longer valid.

I suggested running sp_refreshview 'Schema.View' for each view which would highlight any which aren't able to be resolved. However, the poster came back with a solution which made use of the INFORMATION_SCHEMA view VIEW_TABLE_USAGE. This was something that had slipped under my radar in the past so i thought i'd take a look into it to and see how useful it could be.

On the face of it, the query posted on the forums seemed to be tickety boo! However, on closer inspection, i noticed that it didn't have quite the behaviour i expected. See the following example for an explanation.


-- create some test objects
CREATE DATABASE [Test]
GO
USE [Test]
GO
CREATE SCHEMA schemaa
GO
CREATE SCHEMA schemab
GO
CREATE TABLE schemaa.tbla
(
int1 INT,
int2 INT
)
GO
CREATE TABLE schemaa.tblb
(
int1 INT,
int2 INT
)
GO
CREATE VIEW schemaa.vwa
AS
SELECT a.*
FROM schemaa.tbla a
INNER JOIN schemaa.tblb b
ON a.int1 = b.int1
GO
-- check the results in INFORMATION_SCHEMA.
-- Perfect! 2 rows to appear for each table in SchemaA.vwA
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
-- now remove one of the tables
DROP TABLE schemaa.tbla
GO
-- check the results in INFORMATION_SCHEMA.
-- Perfect! only 1 row appears in SchemaA.vwA as expected
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
GO
-- recreate the dropped table
CREATE TABLE schemaa.tbla
(
int1 INT,
int2 INT
)
GO
-- sp_refreshview
EXEC Sp_refreshview 'SchemaA.vwA'
GO
-- check the results again in INFORMATION_SCHEMA.
-- Perfect! 2 rows to appear for each table in SchemaA.vwA
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
GO
-- this time change schemas of objects
ALTER SCHEMA schemab transfer schemaa.tbla
ALTER SCHEMA schemab transfer schemaa.tblb
ALTER SCHEMA schemab transfer schemaa.vwa
GO
-- check the results in INFORMATION_SCHEMA.
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
-- again 2 rows BUT the VIEW_DEFINTION still shows references to the old schemas.
-- so you could be fooled to thinking that this view is still valid when it isn't
GO
-- try and run refreshview
EXEC Sp_refreshview 'SchemaB.vwA'
-- ha ha, an error!
GO

So you can't rely on INFORMATION_SCHEMA to be a true indicator of whether your views are valid/orphaned or not. Instead, the safest way is to run sp_refreshview to find any that are unable to be resolved.