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

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

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

declare @tablename_mask varchar(50)set @tablename_mask='A表'declare @tableid intprint @tablename_maskselect @tableid=idfrom sysobjectswhere type in ('U' ,'S')and name like @tablename_maskprint @tableid--下面declare @tablename_mask_new varchar(50)set @tablename_mask_new='A表declare @tableid_new intprint @tablename_mask_newselect @tableid_new=idfrom sysobjectswhere type in ('U' ,'S')and name like @tablename_mask_newprint @tableid_newselect * 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 clenfrom syscolumns C join sys.types D on C.xtype=D.system_type_idwhere C.id = @tableidand 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 clenfrom syscolumns C join sys.types D on C.xtype=D.system_type_idwhere C.id = @tableid_newand C.type <> 37 and D.name<>'sysname') yon x.name=y.name where x.clen!=y.clen

查询表列中的字段

declare @tablename_mask varchar(50)set @tablename_mask='表名'declare @tableid intprint @tablename_maskselect @tableid=idfrom sysobjectswhere type in ('U' ,'S')and name like @tablename_maskprint @tableidselect 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 '' endfrom syscolumns C join sys.types D on C.xtype=D.system_type_idwhere C.id = @tableidand C.type <> 37 and D.name<>'sysname'

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。