hi all,

how to find difference in column/fields between 2 different database.....as i am comparing columns of same table from different database.

Thanks

Hi Ranjan,

If those two databases are in the same instance, you can see below statement.

;WITH tbl1 AS (
    SELECT t.name AS TableName, c.Name AS ColumnName
    FROM dbname1.sys.tables t
    INNER JOIN dbname1.sys.columns c ON t.object_id = c.object_id
    WHERE t.name = 'tablename'
),tbl2 AS (
    SELECT t.name AS TableName, c.Name AS ColumnName
    FROM dbname2.sys.tables t
    INNER JOIN dbname2.sys.columns c ON t.object_id = c.object_id
    WHERE t.name = 'tablename'
)
SELECT * FROM TBL1 FULL JOIN TBL2 ON TBL1.ColumnName = TBL2.ColumnName

If those two databases resides on different instances, use linked server and run the above statement with qualified table names like servername.dbname.sys.all_columns.