Monday 11 June 2012

List all invalid views



declare @t TAble (Id int identity(1, 1) Primary key, vName varchar(max), isComplete bit)
declare @badViews Table (vName varchar(max), errNumber int, errMsg varchar(max))
Insert into @t
SELECT [name], 0
FROM sysObjects
WHERE xType = 'V'
AND [name] NOT IN ('syssegments', 'sysconstraints')
AND category=0
ORDER BY crdate

Declare @id int, @tView varchar(max)

set nocount on

while (1 = 1)
Begin
set @id = null
select top 1 @id = Id, @tView = vName from @t
where isComplete = 0

if (@id is null) break;

BEGIN TRY
exec sp_refreshview @tView
Print @tView + ' Valid'
END TRY
BEGIN CATCH
if (ERROR_NUMBER() != 15165) -- Ignore views with Schema binding
Begin
INSERT into @badViews VALUES (@tView, ERROR_NUMBER(), ERROR_MESSAGE())
Print @tView + ' InValid'
End
Else
Print @tView + ' Ignored'
END CATCH


update @t set isComplete = 1 where id = @id
End

select * from @badViews


No comments:

Post a Comment