［SQL］View 你還活著嗎?
最近遇上了系統upgrade, 修改,刪除了很多table, 當然很多view也跟著掛點.
我想就算善意宣導當開發團隊修改table時, 一定要把依賴者修改到好, 也很難做到吧.
索性寫個procedure, touch 一下每一個view, 如果有問題, 馬上就可以看到...
go
Declare @ViewName nvarchar(100)
declare ViewList cursor for
select table_name from INFORMATION_SCHEMA.VIEWS order by 1
Open ViewList
FETCH NEXT FROM ViewList
INTO @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
print '<<<< Executing ' + @ViewName + ' >>>>'
Exec( 'select count(*) from ' + @ViewName )
print ' '
FETCH NEXT FROM ViewList
INTO @ViewName
END
Close ViewList
DEALLOCATE ViewList