对比两表数据一样升序不一样的列
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'