接上篇
-- =============================================
-- Author: <Author,xiaoep>
-- Create date: <Create Date,2012-05-14>
-- Description: <Description,compare table view and procedure between different database>
-- =============================================
ALTER PROCEDURE [dbo].[CheckDatabaseName]
-- Add the parameters for the stored procedure here
(@base1 varchar(64),@base2 varchar(64),@output_table varchar(64)='CompareTable')
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @v_sql varchar(4000)
Declare @v_exist int
set @v_exist = 0
if not exists(select 1 from sys.all_objects where object_id = object_id(@output_table))
begin
--create table
set @v_sql = 'create table '+@output_table+' (per_time date,base1_name varchar(64),object_name varchar(64),'+
'object_type varchar(32),base2_name varchar(64),comm varchar(1000))'
execute (@v_sql )
end
select @v_exist=dbid from sys.sysdatabases where name = @base1
if (@v_exist is not null) and (@v_exist > 0)
begin
select v_exist=dbid from sys.sysdatabases where name = @base2
if (@v_exist is not null) and (@v_exist >0)
begin
--执行比对数据库
execute CompareBaseObject @base1,@base2,@output_table
end
else
begin
select @base2+'库不存在,请检验数据库名称!'
end
end
else
begin
select @base1+'库不存在,请检验数据库名称!'
end
END