SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_sys_refresh_all_views]
as

set nocount on 

declare @sourceControlledObjetsHasData bit = case when exists(select 1 from nm_reference_database_objects) then 1 else 0 end 


declare @tblviews TABLE(id int identity(1,1), view_name sysname)

insert into @tblviews (view_name)
select Table_Name 
from information_Schema.tables t
	left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = t.TABLE_SCHEMA and nrdo.nrdo_ObjectName =  t.Table_Name and nrdo.nrdo_ObjectType = 'V'
where Table_Type = 'View' and ObjectProperty(Object_ID(Table_Name),'ISMSSHIPPED') = 0
and		(@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)


declare @min_id int
declare @max_id int 
declare @id int
declare @view_name sysname
declare @sql varchar(500)

select @min_id = min(id), @max_id = max(id)
from @tblviews

set @id = 1

while @id <= @max_id
begin
	select @view_name = view_name
	from @tblviews
	where id = @id
	
	set @sql = 'exec sp_refreshview [' + @view_name + ']'

    print @view_name + ' start'
	exec(@sql)
	
	print @view_name + ' ok.'
	
	set @id = @id + 1
end 
GO
