Sqlserver compares columns in two tables

I. Problems
Two tables with 40 or 50 columns are given to find their same columns and different columns.

2. query the columns in two tables. A temporary table exists.

-- # A, # B is a temporary table, which is automatically deleted after the current connection is disconnected
-- RANK () OVER (order by syscolumns. name DESC) AS is supported BY SQL2005, and the auto-increment sequence number is added before each record row.
-- The IDENTITY (INT,) function must be used with.

1. Store the column of the table into the name of the table # a -- 'destbl'

Select * into # a from (select RANK () OVER (order by syscolumns. name DESC) AS No., syscolumns. name
From syscolumns, sysobjects
Where syscolumns. [id] = sysobjects. [id]
And sysobjects. [name] = 'destbl') as t

Select * from #

1 Name
2 course
3 id
4 cno

2. Store the table columns in the # B -- 'student 'table name

Select No. = IDENTITY (INT,), syscolumns. name
Into # B from syscolumns, sysobjects
Where syscolumns. [id] = sysobjects. [id]
And sysobjects. [name] = 'student'

Select * from # B

1 id
2 name
3 cno

Iii. analyze and compare the similarities and differences of each table column

Use the following statement or make a comparison later
Select * from # B where name in (select name from #)
Select * from # a where name not in (select name from # B)
Select * from # a, # B where a. name = B. name
Select * from # a left join # B on a. name = B. name