Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

compare column names from two tables in same database Expand / Collapse
Author
Message
Posted Friday, May 16, 2008 11:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 18, 2009 3:34 PM
Points: 4, Visits: 55
I'm not a full-time DBA, so excuse my style of expressing my question.

I have a database which has 2 tables in SQL 2005. Both these tables have similar column names, EXCEPT for new extra columns in FY2007_DATA. I can visually see the difference in columns in Database Diagrams.

My goal is to :- I want to compare FY2007 tbl column names to FY2006 column names and display only those columns as results that do not match.

Tbl 1 :- FY2006_DATA

Tbl 2:- FY2007_DATA

With online reading and help I have managed to get this script to do exactly opposite of what i want. Below is the query

/* This query compares the column names from two tables and displays the ones that have an exact match. It does not care for case-sensitiveness */

Select a.Table_Name, a.Column_Name, (b.Table_Name), (b.column_name)
From [2006-2011].INFORMATION_SCHEMA.Columns AS a
Join [2006-2011].INFORMATION_SCHEMA.Columns AS b on a.Column_Name = b.Column_Name
Where a.TABLE_NAME = 'FY2006_DATA'
And b.TABLE_NAME = 'FY2007_DATA'
AND a.Column_Name IN
(Select Column_Name = LEFT(c.column_name, 20) FROM
[H1B_2006-2011].INFORMATION_SCHEMA.Columns AS c WHERE c.TABLE_NAME = 'FY2007_DATA' )


When I change "AND a.Column_Name IN.." to "AND a.Column_Name NOT IN.." so that the results will (should) display the extra columns in FY2007, in fact I do not see any results, but query executes perfect.

How can I achieve my goal.?

Thank you
Post #502469
Posted Monday, May 19, 2008 6:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
You want to change the INNER join to a FULL OUTER join and then use the WHERE to give you records that do not have a match.

SELECT
*
FROM
Information_Schema.Columns A
FULL OUTER JOIN Information_Schema.Columns B ON A.Column_Name = B.Column_Name
AND B.Table_Name = 'FY2007_DATA'
AND B.Table_Schema = 'dbo'
WHERE
A.Table_Schema = 'dbo'
AND A.Table_Name = 'FY2006_DATA'
AND (A.Column_Name IS NULL OR B.Column_Name IS NULL)
Post #502738
Posted Monday, May 19, 2008 4:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 18, 2009 3:34 PM
Points: 4, Visits: 55
thank you . the script works
Post #503223
Posted Friday, April 17, 2009 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 19, 2012 5:49 AM
Points: 1, Visits: 5
Hi,

Following on from the previous post. I want to take the list of columns that match, so change the outer join to an inner join. Then insert a record into A that exist in B.

The reason to use the first query is the colunms in A dont match B and I dont want to have to type out each column manually.

Any Ideas??
Post #699499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse