December 29, 2008 at 2:49 am
Hi,
I want to compare column names of the Two table that reside in the Different database.
Means I have one Table "TableA" in the database "DB1" & I have another table "TableB" in the another database "DB2". I want to comapre Columns of TableA with the TableB.
Can you please give what query will work for this?
Thanks
December 29, 2008 at 3:26 am
One of the diff ways
-- to find out columns of DB1.TableA which are THERE IN DB2.TableA
select * from DB1.information_schema.columns A
WHERE column_name = SOME (SELECT column_name from DB2.information_schema.columns B WHERE B.table_name = 'TableA')
AND A.table_name = 'TableA'
-- to find out columns of DB1.TableA NOT THERE IN DB2.TableA
select * from DB1.information_schema.columns A
WHERE column_name <> ALL (SELECT column_name from DB2.information_schema.columns B WHERE B.table_name = 'TableA')
AND A.table_name = 'TableA'
"Keep Trying"
December 29, 2008 at 3:37 am
You can try something like this:
SELECT * FROM (
SELECT * FROM DB1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableA'
) a FULL OUTER JOIN (
SELECT * FROM DB2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableA'
) b ON a.COLUMN_NAME=b.COLUMN_NAME
WHERE a.COLUMN_NAME IS NULL OR b.COLUMN_NAME IS NULL
OR a.DATA_TYPE<>b.DATA_TYPE OR a.IS_NULLABLE<>b.IS_NULLABLE
OR a.CHARACTER_MAXIMUM_LENGTH<>b.CHARACTER_MAXIMUM_LENGTH
OR a.NUMERIC_PRECISION<>b.NUMERIC_PRECISION OR a.NUMERIC_SCALE<>b.NUMERIC_SCALE
OR a.COLLATION_NAME<>b.COLLATION_NAME -- and maybe some other columns
Razvan Socol
SQL Server MVP
December 29, 2008 at 3:39 am
Download the Redgate Toolbelt trial - the SQLCompare tool should solve your problem.
December 29, 2008 at 11:56 pm
Thanks you very much.
Query works. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply