| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2006-10-12 : 02:54:01
|
Hi all,I know that my question might not have a simple solution but I thought I'd ask anyway since there are so many geniouses here  I have two databases on the same server which are in general identical with the same tables (DB1 and DB2, DB2 is supposed to replace DB1 and they are used for reporting). DB1 has been living for a few years, DB2 is generated automatically by some script I made just recently. My problem is that the two have slightly different datatypes for some columns so I can't compare them directly. For example one column might be decimal(9, 2) on DB1 and decimal(8, 2) on DB2. Another column might be decimal(6, 0) on DB1 and int on DB2. I could print the schema for each table and compare them manually but this database has about 60 tables and over 100 columns in many of the tables so it will be quite a hassle. I also have 5 (x 2) other databases I have to compare so it's quite messy. Is there a way I can automate something so I can find columns that will/can cause datatype conversion errors??--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-12 : 02:59:30
|
Hmmmmmmmmmmmmmm...I just thought of something. Would writing a UNION select between the databases be a good way to test this?? ->SELECT TOP 10 * FROM db1.dbo.mytable1UNION ALLSELECT TOP 10 * FROM db2.dbo.mytable1 --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-12 : 03:44:26
|
Who don't just compare schema structure?select x.table_name, x.column_name from db1.information_schema.columns xjoindb2.information_schema.columns yon x.table_name = y.table_nameand x.column_name = y.column_nameand ( (x.data_type <> y.data_type) or (x.data_type = y.data_type and x.character_maximum_length <> y.character_maximum_length) or (x.data_type = y.data_type and (x.Numeric_Precision <> y.Numeric_Precision or x.numeric_scale <> y.numeric_scale)) ) Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-12 : 03:59:48
|
| Because that would return nearly all the columns. It will i.e. return a difference because decimal(3, 0) isn't the same as int but it will not cause a datatype conversion error...I only need those that will cause a datatype conversion error...That beeing said it was a very handy script...will save it for later --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-12 : 04:03:03
|
| Use the query above. Replace INT, SMALLINT, AND TINYINT with maxlength of 9, 4 and 2 (of copying TO these) and 10, 4 and 3 if copying from these.Or...Change the <> for data length to only >.Peter LarssonHelsingborg, Sweden |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-12 : 06:14:09
|
| Ey...after a bit if tweaking this query is perfect! I need to learn more about these views...but why is it when I open "System Views" in SSMS they don't show up??--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-12 : 06:40:12
|
| Maybe it is some kind of EM?Check/uncheck for view system objects.Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-12 : 07:27:48
|
quote:
but why is it when I open "System Views" in SSMS they don't show up??
may be because they resides in master database !Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
|
Compare tables - cloesly matching datatypes