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

-- =============================================
-- 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].[CompareBaseObjectColumn]
    -- Add the parameters for the stored procedure here
    (@base1 varchar(64),@base2 varchar(64),@table_name varchar(64),@output_table varchar(64)='CompareTable')
AS
BEGIN
    declare @V_sql nvarchar(4000)
    declare @base1_table_row_cnt int
    declare @base2_table_row_cnt int
    declare @base1_table_row varchar(64)
    declare @base2_table_row varchar(64)
    declare @row_name varchar(64)
    declare @row_type varchar(64)
    declare @base1_row_type int
    declare @base2_row_type int
    declare @row1_cnt int
    declare @row2_cnt int
    --select count of table
    set @v_sql = ' select @base1_table_row_cnt = count(name) from '+@base1+'.sys.syscolumns where id=(select id from '+@base1+'.sys.sysobjects where name='''+@table_name+''')'
    execute sp_executesql @v_sql,N'@base1_table_row_cnt varchar(64) output,@base1 varchar(64),@table_name varchar(64)',@row1_cnt output,@base1,@table_name
    
    set @v_sql = ' select @base2_table_row_cnt = count(name) from '+@base2+'.sys.syscolumns where id=(select id from '+@base2+'.sys.sysobjects where name='''+@table_name+''')'
    execute sp_executesql @v_sql,N'@base2_table_row_cnt varchar(64) output,@base2 varchar(64),@table_name varchar(64)',@row2_cnt output,@base2,@table_name
    
    --not consistent  perform two times else one time
    if @row1_cnt <> @row2_cnt

      begin

        set @V_sql= 'DECLARE   cur_sor1  CURSOR   FOR  '+'select name,xtype from ' +@base1+'.sys.syscolumns where id=(select id from '+@base1+'.sys.sysobjects where name='''+@table_name+''') order by name  '
        exec sp_executesql @v_sql
        open cur_sor1
        fetch  cur_sor1  into  @base1_table_row ,@base1_row_type
        
        while  (@@fetch_status=0)   
        begin   
           set @V_sql = 'select @base2_table_row =name,@base2_row_type =xtype from '+@base2+'.sys.syscolumns where  name='''+@base1_table_row+'''   and id=(select id from '+@base2+'.sys.sysobjects where name='''+@table_name+''') order by name  '
           execute sp_executesql @v_sql,N'@base2_table_row varchar(64) output,@base2_row_type int output,@base2 varchar(64),@base1_table_row varchar(64),@table_name varchar(64)',@base2_table_row output,@base2_row_type output,@base2,@base1_table_row,@table_name
           -- table not exist
           if @base2_table_row is null or @base2_table_row=''
           begin
              set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base1+''','+''''+@table_name+''','''+'TABLE'','+''''+@base2+''','''+@base2+'.'''+'+'''+@table_name+''''+'+''column '''+'+'''+@base1_table_row+'''+'' is not exists this object'''+')'
              --set @V_sql = 'insert into '+@output_table+' values(getdate(),'+@base1+','+@table_name+','+'TABLE,'+@base2+','+@base2+'.'+@table_name+' column '+@base2_table_row+' is not exists this object'+')'
              execute (@v_sql)
           end
           else
           begin
             if @base1_row_type <> @base2_row_type
             set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base1+''','+''''+@table_name+''','''+'TABLE'','+''''+@base2+''','''+@base2+'.'''+'+''column '''+'+'''+@base2_table_row+'''+'' Type is not comsistent'''+')'
             --set @V_sql = 'insert into '+@output_table+' values(getdate(),'+@base1+','+@table_name+','+'TABLE,'+@base2+','+@base2+'.'+@table_name+' column '+@base2_table_row+' Type is not comsistent'+')'
              execute (@v_sql)   
           end
           set @base1_table_row = ''
           set @base2_table_row = ''
           set @base1_row_type = ''
           set @base2_row_type = ''
           fetch  cur_sor1  into  @base1_table_row ,@base1_row_type
        end
        
        close  cur_sor1  
        deallocate cur_sor1
      end
     --
      begin
        set @v_sql ='DECLARE   cur_sor2  CURSOR   FOR  '+'select name,xtype from ' +@base2+'.sys.syscolumns where id=(select id from '+@base2+'.sys.sysobjects where name='''+@table_name+''')  '
        exec sp_executesql @v_sql
        open cur_sor2
        fetch  cur_sor2  into  @base2_table_row,@base2_row_type
        while  (@@fetch_status=0)   
        begin   
           set @V_sql = 'select @base1_table_row =name,@base1_row_type=xtype from '+@base1+'.sys.syscolumns where   name='''+@base2_table_row+'''   and id=(select id from '+@base1+'.sys.sysobjects where name='''+@table_name+''') order by name  '
           execute sp_executesql @v_sql,N'@base1_table_row varchar(64) output,@base1_row_type int output,@base1 varchar(64),@base2_table_row varchar(64),@table_name varchar(64)',@row_name output,@row_type output,@base1,@base2_table_row,@table_name
           -- table not exist
           if @row_name is null or @row_name=''
           begin
              set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base2+''','+''''+@table_name+''','''+'TABLE'','+''''+@base1+''','''+@base1+'.'''+''''+@table_name+'''+'''+'+''column '''+'+'''+@base2_table_row+'''+'' is not exists this object'''+')'

              execute (@v_sql)
           end
           else
           begin
             if @row_type <> @base2_row_type
              set @V_sql = 'insert into '+@output_table+' values(getdate(),'+''''+@base2+''','+''''+@table_name+''','''+'TABLE'','+''''+@base1+''','''+@base1+'.'''+'+''column '''+'+'''+@row_name+'''+'' Type is not comsistent'''+')'
              --set @V_sql = 'insert into '+@output_table+' values(getdate()'+@base2+','+@table_name+','+'TABLE,'+@base1+','+@base1+'.'+@table_name+' column '+@base2_table_row+' Type is not comsistent'+')'
              execute (@v_sql)   
           end
           set @row_type = ''
           set @row_name = ''
           fetch  cur_sor2  into  @base2_table_row,@base2_row_type
        end
        close  cur_sor2  
        deallocate cur_sor2
      end
    
END