【IT168技术文档】
下面介绍的是一个自动检测两个数据库之间对象的存储过程。
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值会有差别。
-- ============================================================ -- 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
