SQL Server compares differences in 2table fields

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