比较差异数据库中的差异表中的2个差异表中的列名称SQL

Question

我想从差异数据库中的2个差异表中获取列名，并比较它们以查看是否有任何表中有额外的列。他们应该完全匹配。一种可能的解决方案可能是将表中的所有列名与临时表中的所有列名并排比较？请帮助。

IF OBJECT_ID('tempdb..#myTable') IS NOT NULL DROP TABLE #myTable 

CREATE table #myTable (
    table1 varchar(100) null, 
    table2 varchar(100) null 
) 

INSERT INTO #myTable (table1) 
    SELECT name 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('table1') 

select * from #mytable 
DROP TABLE #mytable 

Answer 1

我修改您的查询到该

IF OBJECT_ID('tempdb..#myTable') IS NOT NULL DROP TABLE #myTable 
CREATE table #myTable 
(
rowNum int IDENTITY(1,1), 
table1 varchar(100) null 
) 
GO 
IF OBJECT_ID('tempdb..#myTable2') IS NOT NULL DROP TABLE #myTable2 
CREATE table #myTable2 
(
rowNum int IDENTITY(1,1), 
table2 varchar(100) null 
) 
GO 

USE database1 --your 1st database name here 
GO 

INSERT INTO #myTable (table1) 
     (
     SELECT 
      name 
     FROM sys.columns 
     WHERE object_id = OBJECT_ID('Table_1')) 
GO 

USE database2 -- your 2nd database name here 
GO 

INSERT INTO #myTable2 (table2) 
     (
     SELECT 
      name 
     FROM sys.columns 
     WHERE object_id = OBJECT_ID('Table_2')) 
GO 


SELECT table1,table2 
FROM #myTable m 
FULL OUTER JOIN #myTable2 m2 ON m.rowNum = m2.rowNum 
ORDER BY table1,table2 
DROP TABLE #mytable 
DROP TABLE #mytable2