Because of the 2 databases before and after the project, it is necessary to change the structure of the statistical database, the change of each table needs to be counted, because the Manual check is a waste of time, wrote a little code:
1. Number of fields in the statistics table (how many columns are queried):
Select count (name) from syscolumns where id=object_id (' Table name ')
Eg:select count (name) from syscolumns where id=object_id (' t_dk ')
2. Querying database field names (which fields are in the table)
Select Name
From database name. dbo.syscolumns
Where Id= (
select ID from database name. dbo.sysobjects where name= ' table name '
)
eg
Select Name
From Catsic_Compare0803DiLong_2017080311.dbo.syscolumns
Where Id= (
Select ID from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects where name= ' T_cbjzc '
)
3. Compare the differences between the corresponding tables in two databases (the fields corresponding to the query table are the same)
This section is written based on 2:
SELECT * FROM (
Select Name
From Database A.dbo.syscolumns
Where Id= (
Select ID from Database a.dbo.sysobjects
Where name= ' table name a ')
) T1 full OUTER JOIN (
Select name from Database b.dbo.syscolumns
Where Id= (
Select ID from Database b.dbo.sysobjects
Where name= ' Table B '
)
) T2 on T1.name=t2.name
eg
SELECT * FROM (
Select Name
From Catsic_Compare0803DiLong_2017080311.dbo.syscolumns
Where Id= (
Select ID from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects
where name= ' T_cbjzc ')
) T1 full OUTER JOIN (
Select name from Catsicgl_43_2016Eroad_2017111110.dbo.syscolumns
Where Id= (
Select ID from Catsicgl_43_2016Eroad_2017111110.dbo.sysobjects
where Name= ' T_cbjzc '
)
) T2 on T1.name=t2.name
Show only fields with field field names that differ, add a condition where t1.name is null or t2.name is NULL
That's all code:
SELECT * FROM (
Select Name
From Catsic_Compare0803DiLong_2017080311.dbo.syscolumns
Where Id= (
Select ID from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects
where name= ' T_cbjzc ')
) T1 full OUTER JOIN (
Select name from Catsicgl_43_2016Eroad_2017111110.dbo.syscolumns
Where Id= (
Select ID from Catsicgl_43_2016Eroad_2017111110.dbo.sysobjects
where Name= ' T_cbjzc '
)
) T2 on T1.name=t2.name
Where t1.name is null or t2.name is NULL
SQL Server beginners, encourage reprint, learn together
SQL Server compares differences in 2table fields