¿Cómo identifico vistas con dependencies rotas en SQL Server?

Tenemos una gran cantidad de vistas en una database henetworkingada a la que a algunos les faltan dependencies (tabla o incluso otras vistas).

¿Cuál es la mejor manera de identificar las vistas que tienen dependencies faltantes?

DECLARE @stmt nvarchar(max) = '' DECLARE @vw_schema NVARCHAR(255) DECLARE @vw_name varchar(255) IF OBJECT_ID('tempdb..#badViews') IS NOT NULL DROP TABLE #badViews IF OBJECT_ID('tempdb..#nulldata') IS NOT NULL DROP TABLE #nulldata CREATE TABLE #badViews ( [schema] NVARCHAR(255), name VARCHAR(255), error NVARCHAR(MAX) ) CREATE TABLE #nullData ( null_data varchar(1) ) DECLARE tbl_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT name, SCHEMA_NAME(schema_id) AS [schema] FROM sys.objects WHERE type='v' OPEN tbl_cursor FETCH NEXT FROM tbl_cursor INTO @vw_name, @vw_schema WHILE @@FETCH_STATUS = 0 BEGIN SET @stmt = 'SELECT TOP 1 * FROM ' + @vw_schema + N'.' + @vw_name BEGIN TRY INSERT INTO #nullData EXECUTE sp_executesql @stmt END TRY BEGIN CATCH IF ERROR_NUMBER() != 213 BEGIN INSERT INTO #badViews (name, [schema], error) values (@vw_name, @vw_schema, ERROR_MESSAGE()) END END CATCH FETCH NEXT FROM tbl_cursor INTO @vw_name, @vw_schema END CLOSE tbl_cursor -- free the memory DEALLOCATE tbl_cursor SELECT * FROM #badViews DROP TABLE #badViews DROP TABLE #nullData 

Actualización 2017

Se actualizó la respuesta según la respuesta de @whyaw.

También he corregido un error para los valores calculados en las declaraciones de selección. Parece SELECT TOP 1 NULL from vwTest no arroja un error cuando vwTest contiene una columna como digamos 1/0 as [Col1] , pero SELECT TOP 1 * from vwTest arroja una exception.

Prueba esto

Llame a sp_refreshsqlmodule en todos los procedimientos almacenados no vinculados al esquema:

 DECLARE @template AS varchar(max) SET @template = 'PRINT ''{OBJECT_NAME}'' EXEC sp_refreshsqlmodule ''{OBJECT_NAME}'' ' DECLARE @sql AS varchar(max) SELECT @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}', QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)) FROM INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), N'IsSchemaBound') IS NULL OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), N'IsSchemaBound') = 0 EXEC ( @sql ) 

Esto funciona para todas las vistas, funciones y SP. Los objects Schemabound no tendrán problemas y esto no se puede ejecutar en ellos, por eso están excluidos.

Tenga en count que aún es posible que los SP fallen en el time de ejecución debido a que faltan tablas, esto es equivalente a intentar ALTERAR el procedimiento.

Tenga en count también que, al igual que ALTER, perderá properties extendidas en las UDF, las desactivé y las restauré después.

La solución de Adrian Iftode es buena, pero falla si hay vistas que no están asociadas con el esquema pnetworkingeterminado. La siguiente es una versión revisada de su solución que tiene en count el esquema, al time que proporciona información de error contra cada vista anómala (probado en SQL Server 2012):

 DECLARE @stmt NVARCHAR(MAX) = ''; DECLARE @vw_schema NVARCHAR(255); DECLARE @vw_name NVARCHAR(255); CREATE TABLE #badViews ( [schema] NVARCHAR(255) , name NVARCHAR(255) , error NVARCHAR(MAX) ); CREATE TABLE #nullData ( null_data VARCHAR(1) ); DECLARE tbl_cursor CURSOR FORWARD_ONLY READ_ONLY FOR SELECT SCHEMA_NAME(schema_id) AS [schema] , name FROM sys.objects WHERE [type] = 'v'; OPEN tbl_cursor; FETCH NEXT FROM tbl_cursor INTO @vw_schema, @vw_name; WHILE @@FETCH_STATUS = 0 BEGIN SET @stmt = CONCAT(N'SELECT TOP 1 NULL FROM ', @vw_schema, N'.', @vw_name); BEGIN TRY -- silently execute the "select from view" query INSERT INTO #nullData EXECUTE sp_executesql @stmt; END TRY BEGIN CATCH INSERT INTO #badViews ([schema], name, error) VALUES (@vw_schema, @vw_name, ERROR_MESSAGE()); END CATCH FETCH NEXT FROM tbl_cursor INTO @vw_schema, @vw_name; END CLOSE tbl_cursor; DEALLOCATE tbl_cursor; -- print the views with errors when executed SELECT * FROM #badViews; DROP TABLE #badViews; DROP TABLE #nullData; 

Si está utilizando SQL Server 2005 o 2008, puede importar el proyecto a Visual Studio 2008 o 2010 y analizar las dependencies rotas del proyecto de Visual Studio

Haría una copy de security de la database, la restauraría en mi máquina de desarrollo, crearía un script con todas las vistas en una nueva window en el server de administración, descartaría todas las vistas e intentaría ejecutar el script. Siempre que una vista esté "dañada", la ejecución fallará con un post de error, por ejemplo, tabla o columna no existente.