April 2, 2009 at 1:11 pm
Hi,
In my current development project I need to compare a set of tables and see whether we are missing any columns or any new columns in the list.
To further explain my scenario clearly. Say there are 2 tables Work_Table-1 & Table-1.
The work_Table-1 is a dynamically generated table from an XML file, so I need to check whether we are missing any columns or there any new columns added to the table as a part of the import. The comparison is done against the other set of tables already in the database(E.x Table-1).
Please let me know your thoughts on how to approach this issue.
TIA.
April 2, 2009 at 1:14 pm
Insert the XML into a temp table, then compare tempdb.sys.columns to sys.columns in the database where the permanent table is, filtering by object_id in both cases of course. A full outer join will tell you exactly which are missing and which are extra.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 2:16 pm
That was a lightning fast reply. Thank You.
Below is the code I ended up with. Please let me know if you have other ideas.
SELECT A.tblname , B.tblname, A.colname, B.colname
FROM (select C.name AS colname, T.name AS tblname
From SYS.COLUMNS C
INNER JOIN SYS.TABLES T
ON C.object_id = T.object_id --) A
WHEREleft(T.name,5) <> 'work_')A
RIGHT OUTER JOIN
(select C.name AS colname, T.name AS tblname
From SYS.COLUMNS c
INNER JOIN SYS.TABLES T
ON C.object_id = T.object_id--) B
WHERE left(T.name,5) = 'work_') B
ON A.colname = B.colname
WHERE A.colname is null
April 3, 2009 at 7:54 am
If you change the right outer join to a full outer join, and play around with the Where clause a little bit, you'll get both extra columns, and missing columns, if that matters. Otherwise, it looks about right.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply