SQL Server: compare columns in two tables

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 ;)

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'

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.

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