比对两数据库中表与表 字段与字段的不同(3)

-- =============================================
-- Author:        <Author,xiaoep>
-- Create date: <Create Date,2012-05-14>
-- Description:    <Description,compare table view and procedure between different database>
-- compare database  table_column_name and   type
--                   view_name
--                   procedure_name
-- =============================================
ALTER PROCEDURE [dbo].[CompareBaseObject]
    -- Add the parameters for the stored procedure here
    (@base1 varchar(64),@base2 varchar(64),@output_table varchar(64)='CompareTable')
AS
BEGIN
    declare @V_sql nvarchar(4000)
    declare @base1_table_cnt int
    declare @base2_table_cnt int
    declare @base1_table_name varchar(64)
    declare @base2_table_name varchar(64)
    declare @table_name varchar(64)
    declare @cnt1   int
    declare @cnt2   int
    --table
    set @base1_table_cnt = 0;
    set @base2_table_cnt = 0;
    set @v_sql = ' select @base1_table_cnt = count(name) from '+@base1+'.sys.sysobjects where xtype = ''U'''
    execute sp_executesql @v_sql,N'@base1_table_cnt int output,@base1 varchar(64)',@cnt1 output,@base1
    
    set @v_sql = ' select @base2_table_cnt = count(name) from '+@base2+'.sys.sysobjects where xtype = ''U'''
    execute sp_executesql @v_sql,N'@base2_table_cnt int output,@base2 varchar(64)',@cnt2 output,@base2
    
    --not consistent  perform two times else one time
    if @cnt1 <> @cnt2

      begin

        set @v_sql = 'DECLARE   tmp_cursor  CURSOR   FOR  '+'select name from ' +@base1+'.sys.sysobjects where xtype = ''U'' order by name'  
        exec sp_executesql @v_sql
        open tmp_cursor
        fetch  tmp_cursor  into  @base1_table_name   
        
        while  (@@fetch_status=0)   
        begin   
           set @V_sql = 'select @base2_table_name =name from '+@base2+'.sys.sysobjects where  xtype=''U'' and name = '''+@base1_table_name+''''
           execute sp_executesql @v_sql,N'@base2_table_name varchar(64) output,@base2 varchar(64),@base1_table_name varchar(64)',@table_name output,@base2,@base1_table_name
           -- table not exist
           if @table_name is null or @table_name=''
           begin
              set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base1+''','+''''+@base1_table_name+''','''+'TABLE'','+''''+@base2+''','''+@base2+''''+'+'' is not exists this object'''+')'

              execute (@V_sql)
           end
           else
           begin
             execute CompareBaseObjectColumn @base1,@base2,@table_name,@output_table
             
           end
           set @table_name = ''
           fetch  tmp_cursor  into  @base1_table_name  
        end
        close  tmp_cursor   
        deallocate tmp_cursor  
      end
     --
      begin
        set @v_sql = 'DECLARE   cursor_temp  CURSOR   FOR '+'select name from ' +@base2+'.sys.sysobjects where xtype = ''U'' order by name'
        exec sp_executesql @v_sql
        open cursor_temp
        fetch  cursor_temp  into  @base2_table_name  
        while  (@@fetch_status=0)    
        begin   
           set @V_sql = 'select @base1_table_name =name from '+@base1+'.sys.sysobjects where  xtype=''U'' and name = '''+@base2_table_name+''''
            execute sp_executesql @v_sql,N'@base1_table_name varchar(64) output,@base1 varchar(64),@base2_table_name varchar(64)',@table_name output,@base1,@base2_table_name
           -- table not exist
           if @table_name is null or @table_name=''
           begin
              set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base2+''','+''''+@base2_table_name+''','''+'TABLE'','+''''+@base1+''','''+@base1+''''+'+'' is not exists this object'''+')'
              
              execute (@V_sql)

           end
           else
           begin
             execute CompareBaseObjectColumn @base1,@base2,@base2_table_name,@output_table
             
           end
           set @table_name = ''
           fetch  cursor_temp  into  @base2_table_name  
        end
        close  cursor_temp  
        deallocate cursor_temp
      end
      set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base2+''','+''''+@base2_table_name+''','''+'TABLE'','+''''+@base1+''','''+@base1+''''+'+'' Scan one time '''+')'

      execute (@V_sql)
      -- view
       
ENd