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