ITPub博客

首页 > 数据库 > 数据库开发技术 > 自动检测两个数据库之间对象的存储过程

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

原创 数据库开发技术 作者:kitesky 时间:2005-01-21 11:29:17 0 删除 编辑

-- ============================================================
-- 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 关于default约束
请登录后发表评论 登录
全部评论

注册时间:2009-04-22

  • 博文量
    273
  • 访问量
    2173195