SQL新旧数据库结构一致，所有表数据的比较SQL

最新推荐文章于 2024-08-15 16:53:53 发布

wangxianshou

最新推荐文章于 2024-08-15 16:53:53 发布

阅读量444

点赞数

分类专栏： MSSQL 文章标签： sql 数据库 table schema join delete

本文链接：https://blog.csdn.net/wangxianshou/article/details/6691937

版权

MSSQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

IF OBJECT_ID('TEMPDB..#TEMPTABLE') IS NOT NULL      
DROP TABLE #TEMPTABLE      
      
select distinct       
a.TABLE_NAME      
,a.TABLE_CATALOG+'.'+a.TABLE_SCHEMA+'.' AS SV105      
,'[192.168.1.130].'+a.TABLE_CATALOG+'.'+a.TABLE_SCHEMA+'.' as SV130      
,a.COLUMN_NAME      
,a.IS_NULLABLE      
INTO #TEMPTABLE      
from INFORMATION_SCHEMA.COLUMNS as a
inner join  INFORMATION_SCHEMA.TABLES as b
on a.TABLE_CATALOG = b.TABLE_CATALOG
and a.TABLE_SCHEMA = b.TABLE_SCHEMA
and a.TABLE_NAME = b.TABLE_NAME
where  
	b. TABLE_TYPE='BASE TABLE'
      
 DECLARE @TNAME AS VARCHAR(100)      
 DECLARE @SV1 AS VARCHAR(100)      
 DECLARE @SV2 AS VARCHAR(100)      
          
select * from #TEMPTABLE    
    
 WHILE EXISTS(SELECT 1 FROM #TEMPTABLE)      
 BEGIN      
       
    
          
    declare @cname as varchar(200)      
    declare @isnullable as varchar(100)      
    SELECT TOP 1       
    @TNAME =  TABLE_NAME       
    ,@SV1 =SV105      
    ,@SV2 =SV130      
    FROM #TEMPTABLE      
          
    declare @sql as varchar(8000)      
    set @sql=  
    ' select '''+@TNAME+''' as table_name,count(1) as rows from ' + @SV1+@TNAME + CHAR(10)+  
    ' select '''+@TNAME+''' as table_name,count(1) as rows from ' + @SV2+@TNAME + CHAR(10)+  
    ' select '''+@TNAME+''' as table_name,count(1) as rows from ' + @SV1+@TNAME + ' as a ' + CHAR(10) +  
        ' inner join ' +@SV2+@TNAME + ' as b ' + CHAR(10)      
    
    DECLARE ABC CURSOR  FOR      
    SELECT column_name,is_nullable       
    from #TEMPTABLE       
    where TABLE_NAME =@TNAME      
          
    open abc      
    fetch next from abc into @cname,@isnullable      
    declare @index as int      
    set @index = 0      
    WHILE @@fetch_status = 0      
    BEGIN      
    set @index =@index+1      
    set @sql = @sql +       
        case when @index =1 then ' on '       
        else ' and '       
        end       
        +case when @isnullable='YES' then 'isnull(a.'+@cname+',0)'      
        else ' a.'+@cname       
        end      
        +' = '      
        +case when @isnullable='YES' then 'isnull(b.'+@cname+',0)'      
        else ' b.'+@cname       
        end      
        + CHAR(10)      
      
      
    fetch next from abc into @cname,@isnullable      
    end      
    close abc      
    DEALLOCATE abc      
    print @sql      
          
    delete from #TEMPTABLE where TABLE_NAME =@TNAME      
 END