SQL比对数据库之间的区别

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tb1') and type='U')
drop table #tb1;
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tb2') and type='U')
drop table #tb2;
 
create table #tb1(TableName nvarchar(250),ColumnName nvarchar(250),Sequence int,Flag bit,PKey varchar(250),ColumnType varchar(250),
 ColumnByteLength int,ColumnLength int,DecimalLength int,AllowNull bit,DefaultValue varchar(500),ColumnDesc sql_variant)
 
create table #tb2(TableName nvarchar(250),ColumnName nvarchar(250),Sequence int,Flag bit,PKey varchar(250),ColumnType varchar(250),
 ColumnByteLength int,ColumnLength int,DecimalLength int,AllowNull bit,DefaultValue varchar(500),ColumnDesc sql_variant)
 go
 
declare @dbname1 varchar(100);
declare @dbname2 varchar(100);
set @dbname1='ZLDC_CostControl_QATEST';
set @dbname2='ZLDC_CostControl_Staging';


--得到数据库1的结构
exec('insert into #tb1 SELECT
 表名=d.name,字段名=a.name,序号=a.colid,
 标识=case when a.status=0x80 then 1 else 0 end,
 主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and name in (
  SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
   SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
  ))) then 1 else 0 end,
 类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
  默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM '+@dbname1+'..syscolumns a
 left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
 inner join '+@dbname1+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name<>''dtproperties''
 left join '+@dbname1+'..syscomments e on a.cdefault=e.id
 left join '+@dbname1+'.sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
order by a.id,a.colorder')
 
--得到数据库2的结构
exec('insert into #tb2 SELECT
 表名=d.name,字段名=a.name,序号=a.colid,
 标识=case when a.status=0x80 then 1 else 0 end,
 主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and name in (
  SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
   SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
  ))) then 1 else 0 end,
 类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
 默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM '+@dbname2+'..syscolumns a
 left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
 inner join '+@dbname2+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name<>''dtproperties''
 left join '+@dbname2+'..syscomments e on a.cdefault=e.id
 left join '+@dbname2+'.sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select Different=case when a.TableName is null and b.Sequence=1 then N'库1缺少表：'+b.TableName
  when b.TableName is null and a.Sequence=1 then N'库2缺少表:'+a.TableName
  when a.ColumnName is null and exists(select 1 from #tb1 where TableName=b.TableName) then N'库1 ['+b.TableName+N'] 缺少字段：'+b.ColumnName
  when b.ColumnName is null and exists(select 1 from #tb2 where TableName=a.TableName) then N'库2 ['+a.TableName+N'] 缺少字段：'+a.ColumnName
  when a.Flag<>b.Flag then N'标识不同'
  when a.PKey<>b.PKey then N'主键设置不同'
  when a.ColumnType<>b.ColumnType then N'字段类型不同'
  when a.ColumnByteLength<>b.ColumnByteLength then N'占用字节数'
  when a.ColumnLength<>b.ColumnLength then N'长度不同'
  when a.DecimalLength<>b.DecimalLength then N'小数位数不同'
  when a.AllowNull<>b.AllowNull then N'是否允许空不同'
  when a.DefaultValue<>b.DefaultValue then N'默认值不同'
  when a.ColumnDesc<>b.ColumnDesc then N'字段说明不同'
 else '' end,
 *
 from #tb1 a
 full join #tb2 b on a.TableName=b.TableName and a.ColumnName=b.ColumnName
where a.TableName is null or a.ColumnName is null or b.TableName is null or b.ColumnName is null
 or a.Flag<>b.Flag or a.PKey<>b.PKey or a.ColumnType<>b.ColumnType
 or a.ColumnByteLength<>b.ColumnByteLength or a.ColumnLength<>b.ColumnLength or a.DecimalLength<>b.DecimalLength
 or a.AllowNull<>b.AllowNull or a.DefaultValue<>b.DefaultValue or a.ColumnDesc<>b.ColumnDesc
order by isnull(a.TableName,b.TableName),isnull(a.Sequence,b.Sequence)--isnull(a.字段名,b.字段名)
go



		select BudgetType,CostCenterID,CostCenterName,BudgetItemID,BudgetItemName
                            from [dbo].[TB_Payment_Apply]  pa
                            inner join [dbo].[TB_Payment_Apply_Detail] pad
                            on pad.PaymentApplyID =pa.PaymentApplyID
                            where  pad.CostCenterID='DYYZX001'
	                        and year(pad.CreateDateTime)='2018'
	                        and pa.Status='4' and pa.BudgetType='1' 
							--and BudgetItemID like '1.%'