自動檢測兩個資料庫之間物件的儲存過程

-- ============================================================
-- Procedure Name : wh_check_dataobject
-- Function   : check the dataobjects between two database
-- Failure return : 1
-- Success return : 0
--
-- Parameters  :
-- @database_check : the database that is checked
-- @dataobject  : (tb-table, sp-stored procedure, fn-function, tr-trigger, vw-view, all-all objects)
-- author   : royalhigh, Jan, 2005/20
-- ============================================================
IF EXISTS (select * from sysobjects where id = object_id(N'wh_check_dataobject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE wh_check_dataobject
GO

CREATE procedure wh_check_dataobject
 (
 @database_check  varchar(128),
 @dataobject   varchar(3)
 )
--WITH ENCRYPTION
AS
DECLARE @script_table table
 (
  col_scripts varchar(400)
 )
 
DECLARE @object_table table
 (
  col_objects varchar(128)
 )
DECLARE @scripts varchar(400)

BEGIN
 SET NOCOUNT ON

 IF (@database_check IS NULL or @database_check = '')
 BEGIN
  RAISERROR ('Parameter can not be NULL, input them, Please', 16, 1) WITH NOWAIT
  RETURN 1
 END
 -- 1. TABLE CHECK
 IF (@dataobject = 'tb' OR @dataobject = 'all')
 BEGIN
  PRINT 'the inexistent table:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'U'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
  

 END
 
 -- 2. SP CHECK
 IF (@dataobject = 'sp' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent stored procedure:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'P'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END

 -- 3. FUNCTION CHECK
 IF (@dataobject = 'fn' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent function:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype in('FN','TF', 'IF')
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END

 -- 4. TRIGGER CHECK
 IF (@dataobject = 'tr' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent trigger:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'TR'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END

 -- 5. VIEW CHECK
 IF (@dataobject = 'vw' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent view:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'V'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END
 
 IF (@@error <> 0)
 BEGIN
  RETURN 1
 END
END

GO

-- ============================================================

1、使用說明
 檢查X資料庫中是否存在Y版本的所有物件
 (1) 在標準Y版本資料庫建立SP：wh_check_dataobject
 (2) 執行wh_check_dataobject，得到比較結果

2、使用方法
 @database_check ：代表需要檢查的X資料庫名
 @dataobject ：(tb-table, sp-stored procedure, fn-function, tr-trigger, vw-view, all-all objects)
 例如：wh_check_dataobject j_jill, 'all'

3、在SP中，不能呼叫object_id來取物件id值，而只能用name做為條件在sysobjects中檢索來判斷物件是否存在。
  因為用“SELECT 1 FROM ' + @database_check + '.dbo.sysobjects”切換過資料庫，而object_id只取當前資料庫，
  二者取的不是同一個庫，id值會有差別；

[@more@]