-- ============================================================
-- 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值会有差别;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66009/viewspace-787548/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/66009/viewspace-787548/