|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, January 04, 2009 9:50 AM
Points: 2,
Visits: 22
|
|
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
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Tuesday, June 30, 2009 11:45 PM
Points: 1,713,
Visits: 1,364
|
|
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"
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Thursday, June 25, 2009 12:17 AM
Points: 687,
Visits: 94
|
|
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
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Monday, June 22, 2009 5:25 AM
Points: 61,
Visits: 81
|
|
Download the Redgate Toolbelt trial - the SQLCompare tool should solve your problem.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, January 04, 2009 9:50 AM
Points: 2,
Visits: 22
|
|
Thanks you very much.
Query works. :)
|
|
|
|
