SQL Server: compare columns in two tables

have a look at Red Gate SQL Compare\n\nOtherwise here is a start (for sql server)\n\nselect \n so.name as [table],\n sc.name as [column],\n sc.type, sc.length, sc.prec, sc.scale, sc.collation\nfrom \n sysobjects so\n inner join syscolumns sc ON so.id = sc.id\n\nwhere so.type='u'\n\norder by so.name, sc.colorder\n\n\nyou can have a look at the \n\n - INFORMATION_SCHEMA.TABLES\n - INFORMATION_SCHEMA.COLUMNS\n - INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS\n - INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n - INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n\n\ntables if you want to go deeper..\n\n[update] \n\nUsing the INFORMATION_SCHEMA tables\n\nSELECT\n [table].TABLE_NAME AS [Table_Name],\n [column].COLUMN_NAME AS [Column_Name],\n COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],\n [column].DATA_TYPE AS [datatype],\n [column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],\n [column].NUMERIC_PRECISION AS Numeric_precision,\n [column].ORDINAL_POSITION AS [order],\n [column].COLUMN_DEFAULT AS [defaultvalue],\n [column].IS_NULLABLE AS [nullable]\nFROM \n INFORMATION_SCHEMA.TABLES [table] INNER JOIN \n INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME\nWHERE\n [table].TABLE_TYPE = 'BASE TABLE'\n AND [table].TABLE_NAME <> 'sysdiagrams'\nORDER BY \n [table].TABLE_NAME ASC, \n [column].ORDINAL_POSITION ASC\n",

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. \n\nEven 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. \n\nIf you really need to use SQL for this you can try really simple query like this and then build on top of this.\n\nselect T.name, C.*\nfrom sys.tables T\ninner join sys.columns C on T.object_id = C.object_id\nwhere T.name = 'table_name'\n",

This works for me (had the same problem and just compiled my solution)\n\nDECLARE @TableOne VARCHAR(2048) = '',\n @TableTwo VARCHAR(2048) = ''\n\n-- In TableOne but not in TableTwo\nSELECT DISTINCT\n @TableOne AS [First table],\n '>>' AS Dir, --Direction\n @TableTwo AS [Second table],\n a.COLUMN_NAME,\n a.DATA_TYPE \n FROM INFORMATION_SCHEMA.COLUMNS a\n WHERE a.COLUMN_NAME NOT IN (SELECT COLUMN_NAME\n FROM INFORMATION_SCHEMA.COLUMNS b\n WHERE b.TABLE_NAME = @TableTwo)\n AND a.TABLE_NAME = @TableOne\nUNION ALL\n-- In TableTwo but not in TableOne\nSELECT DISTINCT\n @TableOne AS [First table],\n '<<' AS Dir, --Direction\n @TableTwo AS [Second table],\n a.COLUMN_NAME,\n a.DATA_TYPE \n FROM INFORMATION_SCHEMA.COLUMNS a\n WHERE a.COLUMN_NAME NOT IN (SELECT COLUMN_NAME\n FROM INFORMATION_SCHEMA.COLUMNS b\n WHERE b.TABLE_NAME = @TableOne)\n AND a.TABLE_NAME = @TableTwo\n ORDER BY Dir DESC, COLUMN_NAME ASC\n\n\njust set values for @TableOne and @TableTwo and run the script ;)",

Realy it is a big script. :)\n\nUse red gate sql compare. They offer you 14-day free trial \n\nIf you realy need script it can be a text and than you can compare both by using any text comparer.",