自动检测两个数据库之间对象的存储过程

-- ============================================================
-- 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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-787548/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-787548/