SQL Server: compare columns in two tables

Question

I've recently done a migration from a really old version of some application to the current version and i faced some problems while migrating databases.

I need a query that could help me to compare columns in two tables. I mean not the data in rows, I need to compare the columns itself to figure out, what changes in table structure I've missed.

Answer 1

I found Qcpbraca's solution most in line with what I was looking for but felt, at least for my sake, that it visually was a little hard to look at the results and know which column was missing. I also often have column name matches but data type mis-matches so I added that to the code below as well.

The following script expands on Qcpbraca's script so if you find this helpful enough to vote it up, please consider voting Qcpbraca's answer up as well.

DECLARE @Table1 VARCHAR(2048) = 'Table_1',
        @Table2 VARCHAR(2048) = 'Table_2'

-- Table 1 Columns into #temp_table_1
SELECT DISTINCT
       a.COLUMN_NAME AS [Column Name],
       a.DATA_TYPE as [Data Type],
       a.CHARACTER_MAXIMUM_LENGTH,
       a.NUMERIC_PRECISION,
       a.NUMERIC_SCALE
into #temp_table1
FROM INFORMATION_SCHEMA.COLUMNS a
where a.TABLE_NAME = @Table1
order by a.COLUMN_NAME

-- Table 2 Columns into #temp_table_2
SELECT DISTINCT
       a.COLUMN_NAME AS [Column Name],
       a.DATA_TYPE as [Data Type],
       a.CHARACTER_MAXIMUM_LENGTH,
       a.NUMERIC_PRECISION,
       a.NUMERIC_SCALE
into #temp_table2
FROM INFORMATION_SCHEMA.COLUMNS a
where a.TABLE_NAME = @Table2
order by a.COLUMN_NAME

select 
    @Table1 [Table 1],
    isnull(t1.[Column Name],'') [Table 1 Column Name],
    isnull(t2.[Column Name],'') [Table 2 Column Name],
    @Table2 [Table 2],
    isnull(t1.[Data Type],'') [Table 1 Column Type],
    isnull(t2.[Data Type],'') [Table 2 Column Type],
    isnull(cast(t1.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t1.NUMERIC_PRECISION as varchar(50)),'') + ',' +
        isnull(cast(t1.NUMERIC_SCALE as varchar(50)),'')) [Table 1 Column Precision],
    isnull(cast(t2.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t2.NUMERIC_PRECISION as varchar(50)),'') + ',' +
        isnull(cast(t2.NUMERIC_SCALE as varchar(50)),'')) [Table 2 Column Precision],
    --[Data Type Warning]
        case when isnull(t1.[Column Name],'') = isnull(t2.[Column Name],'') 
                and (
                     isnull(t1.[Data Type],'') <> isnull(t2.[Data Type],'')
                     or
                     isnull(cast(t1.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t1.NUMERIC_PRECISION as varchar(50)),'') + ',' +
                            isnull(cast(t1.NUMERIC_SCALE as varchar(50)),''))
                        <> 
                     isnull(cast(t2.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t2.NUMERIC_PRECISION as varchar(50)),'') + ',' +
                            isnull(cast(t2.NUMERIC_SCALE as varchar(50)),''))
                     )
            then '*** Data Type Mismatch ***' else '' end 
    [Data Type Warning]
from #temp_table1 t1 
    full outer join #temp_table2 t2 on t1.[Column Name] = t2.[Column Name]
where 1=1

drop table #temp_table1, #temp_table2
go

Here is a sample result set: