SQL Server 对比两表数据一样升序不一样的列

 

对比两表数据一样升序不一样的列

declare @tablename_mask varchar(50)
set @tablename_mask='A表'
declare @tableid int
print @tablename_mask
select @tableid=id
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask
print @tableid

--下面
declare @tablename_mask_new varchar(50)
set @tablename_mask_new='A表
declare @tableid_new int
print @tablename_mask_new
select @tableid_new=id
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask_new
print @tableid_new


select * from 
(select C.name,UPPER(D.name) ctype,
case when (D.name='nvarchar' or D.name='varchar') and C.length=-1 then 'max' when D.name='nvarchar' or D.name='varchar' then cast(C.length as varchar(50)) else '' end as clen
from syscolumns C join sys.types D on C.xtype=D.system_type_id
where C.id = @tableid
and C.type <> 37 and D.name<>'sysname'
) x join 
(
select C.name,UPPER(D.name) ctype,
case when (D.name='nvarchar' or D.name='varchar') and C.length=-1 then 'max' when D.name='nvarchar' or D.name='varchar' then cast(C.length as varchar(50)) else '' end as clen
from syscolumns C join sys.types D on C.xtype=D.system_type_id
where C.id = @tableid_new
and C.type <> 37 and D.name<>'sysname'
) y
on x.name=y.name where x.clen!=y.clen

查询表列中的字段

declare @tablename_mask varchar(50)
set @tablename_mask='表名'
declare @tableid int
print @tablename_mask
select @tableid=id
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask
print @tableid

select C.name,UPPER(D.name),
case when (D.name='nvarchar' or D.name='varchar') and C.length=-1 then 'max' when D.name='nvarchar' or D.name='varchar' then cast(C.length as varchar(50)) else '' end
from syscolumns C join sys.types D on C.xtype=D.system_type_id
where C.id = @tableid
and C.type <> 37 and D.name<>'sysname'