Recreate dependencies in database

Create procedure refresh_all_views_in_order
--
-- Created by Claude Fiaux 13-jan-2003
--	Nexans Suisse SA
-- 
-- This procedure refresh all views of current database in good order
-- Execution of this procedure recreate correct dependencies lost after updating views.
-- This procedure just call sp_refreshview and uses temporary table
--
--
-- Execution of this procedure prevent errors in DTS Import/Export wyzard
--        Copy objects and data between SQL Server databases
--
as
begin
-- create temporary tables
	set nocount on
	--drop table #tmp_all_depends
	create table #tmp_all_depends (Master_Object nvarchar(200),levelMO int,typemo nvarchar(30),Object nvarchar(200),typeo nvarchar(30))
-- cursor for access all Views
	DECLARE cur_obj cursor for SELECT id,sysusers.name + '.' + sysobjects.name AS Full_name, dbo.sysobjects.xtype
					FROM dbo.sysobjects INNER JOIN
	                      		     dbo.sysusers ON dbo.sysobjects.uid = dbo.sysusers.uid
					WHERE (dbo.sysobjects.xtype IN ('V', 'FN', 'IF'))
	open cur_obj
-- First loop of refresh views 
-- this loop determine how objects is used by each view
	declare @id_mo as int
	declare @master_object nvarchar(50)
	declare @master_object_type nvarchar(3)

   	FETCH NEXT FROM cur_obj into @id_mo,@master_object,@master_object_type
	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @master_object_type = 'V' 
			exec sp_refreshview @master_object
		else begin
			set @master_object = @master_object
			-- no have à way for refreshing function and inline function !?
			-- exec sp_recompile @master_object
		end
		insert #tmp_all_depends 
		  SELECT DISTINCT 
			dbo.sysusers.name + '.' + dbo.sysobjects.name AS master_object, 
			0 AS levelMO, 
			dbo.sysobjects.xtype AS master_object_type,
			sysusers_1.name + '.' + sysobjects_1.name AS Object, 
			sysobjects_1.xtype AS Object_type
		    FROM dbo.sysdepends 
			INNER JOIN dbo.sysobjects 
				ON dbo.sysdepends.id = dbo.sysobjects.id 
			INNER JOIN dbo.sysobjects sysobjects_1 
				ON dbo.sysdepends.depid = sysobjects_1.id 
			INNER JOIN dbo.sysusers 
				ON dbo.sysobjects.uid = dbo.sysusers.uid 
			INNER JOIN dbo.sysusers sysusers_1 
				ON sysobjects_1.uid = sysusers_1.uid
			WHERE dbo.sysdepends.id = @id_mo
    		FETCH NEXT FROM cur_obj into @id_mo,@master_object,@master_object_type
	END
	close cur_obj
	deallocate cur_obj
-- Determine good order for second refresh loop
--  views dont use another view not need second refresh and not match in #tmp_all_depends 
	declare cur_tmp_all_depends cursor for select distinct master_object from #tmp_all_depends
	open cur_tmp_all_depends
	declare @current nvarchar(50)
	DECLARE @level int, @max_level int
	CREATE TABLE #stack (item char(50), level int)
   	FETCH NEXT FROM cur_tmp_all_depends into @master_object
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @current = @master_object
		delete from #stack
		INSERT INTO #stack VALUES (@current, 1)
		SELECT @level = 1
		SELECT @max_level = 1
		WHILE @level > 0
		BEGIN
		   IF EXISTS (SELECT * FROM #stack WHERE level = @level)
		      BEGIN
		         SELECT @current = item FROM #stack WHERE level = @level
			 if @max_level < @level 
			    set @max_level = @level		        
		         DELETE FROM #stack WHERE level = @level AND item = @current
		         INSERT #stack SELECT Object, @level + 1 FROM #tmp_all_depends 
					WHERE master_object = @current
		         IF @@ROWCOUNT > 0
		            SELECT @level = @level + 1
		      END
		   ELSE
		      SELECT @level = @level - 1
		end
		update #tmp_all_depends set levelmo = @MAX_LEVEL where master_object=@master_object
	   	FETCH NEXT FROM cur_tmp_all_depends into @master_object
	end
	close cur_tmp_all_depends
	DEALLOCATE cur_tmp_all_depends
	drop table #stack
-- Second loop of refresh views 
-- this loop refresh views in good order 
	DECLARE cur_obj_in_order cursor for SELECT Master_Object,typemo from #tmp_all_depends
						ORDER BY levelMO
	open cur_obj_in_order

   	FETCH NEXT FROM cur_obj_in_order into @master_object,@master_object_type
	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @master_object_type = 'V' 
			exec sp_refreshview @master_object
		else begin
			set @master_object = @master_object
			-- no have à way for refreshing function and inline function !?
			-- exec sp_recompile @master_object
		end
    		FETCH NEXT FROM cur_obj_in_order into @master_object,@master_object_type
	END
	close cur_obj_in_order
	deallocate cur_obj_in_order
	drop table #tmp_all_depends
end