SQL Server: compare columns in two tables

I've recently done a migration from a rea开发者_如何学运维lly 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.

have a look at Red Gate SQL Compare

Otherwise here is a start (for sql server)

select 
 so.name as [table],
 sc.name as [column],
 sc.type, sc.length, sc.prec, sc.scale, sc.collation
from 
 sysobjects so
 inner join syscolumns sc ON so.id = sc.id

where so.type='u'

order by so.name, sc.colorder

you can have a look at the

 - INFORMATION_SCHEMA.TABLES
 - INFORMATION_SCHEMA.COLUMNS
 - INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 - INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 - INFORMATION_SCHEMA.KEY_COLUMN_USAGE

tables if you want to go deeper..

[update]

Using the INFORMATION_SCHEMA tables

SELECT
 [table].TABLE_NAME AS [Table_Name],
 [column].COLUMN_NAME AS [Column_Name],
 COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
 [column].DATA_TYPE AS [datatype],
 [column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
 [column].NUMERIC_PRECISION AS Numeric_precision,
 [column].ORDINAL_POSITION AS [order],
 [column].COLUMN_DEFAULT AS [defaultvalue],
 [column].IS_NULLABLE AS [nullable]
FROM 
 INFORMATION_SCHEMA.TABLES [table] INNER JOIN 
 INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
 [table].TABLE_TYPE = 'BASE TABLE'
 AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY 
 [table].TABLE_NAME ASC, 
 [column].ORDINAL_POSITION ASC

I’d really recommend you use third party comparison tool such as SQL Compare already mentioned above or ApexSQL Diff or basically any other tool on the market.

Even though these are commercial tools you can get a free trial and get the job done if you don’t really need to do this daily.

If you really need to use SQL for this you can try really simple query like this and then build on top of this.

select T.name, C.*
from sys.tables T
inner join sys.columns C on T.object_id = C.object_id
where T.name = 'table_name'

This works for me (had the same problem and just compiled my solution)

DECLARE @TableOne VARCHAR(2048) = '',
        @TableTwo VARCHAR(2048) = ''

-- In TableOne but not in TableTwo
SELECT DISTINCT
       @TableOne AS [First table],
       '>>' AS Dir, --Direction
       @TableTwo AS [Second table],
       a.COLUMN_NAME,
       a.DATA_TYPE         
  FROM INFORMATION_SCHEMA.COLUMNS a
 WHERE a.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
                               FROM INFORMATION_SCHEMA.COLUMNS b
                              WHERE b.TABLE_NAME = @TableTwo)
   AND a.TABLE_NAME = @TableOne
UNION ALL
-- In TableTwo but not in TableOne
SELECT DISTINCT
       @TableOne AS [First table],
       '<<' AS Dir, --Direction
       @TableTwo AS [Second table],
       a.COLUMN_NAME,
       a.DATA_TYPE         
  FROM INFORMATION_SCHEMA.COLUMNS a
 WHERE a.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
                               FROM INFORMATION_SCHEMA.COLUMNS b
                              WHERE b.TABLE_NAME = @TableOne)
   AND a.TABLE_NAME = @TableTwo
 ORDER BY Dir DESC, COLUMN_NAME ASC

just set values for @TableOne and @TableTwo and run the script ;)

Realy it is a big script. :)

Use red gate sql compare. They offer you 14-day free trial

If you realy need script it can be a text and than you can compare both by using any text comparer.

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: