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

本文主要是介绍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'

这篇关于SQL Server 对比两表数据一样升序不一样的列的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！