January 9, 2019 at 1:21 pm
Hi,
I have two tables which are very similar, and they would like to make them one table. However, before I can do that I need a way to compare each column name and see what names are in one table and not the other. these two tables are large so, while it could be it would be hard if I have to compare name by name in a list; so some kind of query would be helpful here.
Also, it would be good if I could get things like the data type/size for each field.
Thank you
January 9, 2019 at 2:26 pm
Like this?
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database
I suppose you could easily find non-matching tables by grouping on column name and getting COUNT = 1?
SQLCompare by RedGate does that, I think.
January 9, 2019 at 2:53 pm
itmasterw 60042 - Wednesday, January 9, 2019 1:21 PMHi,
I have two tables which are very similar, and they would like to make them one table. However, before I can do that I need a way to compare each column name and see what names are in one table and not the other. these two tables are large so, while it could be it would be hard if I have to compare name by name in a list; so some kind of query would be helpful here.
Also, it would be good if I could get things like the data type/size for each field.
Thank you
You can get all the column names and properties by querying sys.columns in the database where the table exists. You filter it by table with object_id in sys.columns, e.g.
WHERE OBJECT_ID = OBJECT_ID('YourTableName')
Personally I'd probably not go by name alone as two tables could have the same column names but used differently. But you know the database, business so that may not apply.
I would compare all the properties of the columns that match between the two tables.
Sue
January 10, 2019 at 7:16 am
If you really want to automate this and run it all the time across multiple environments and multiple databases, I'd strongly suggest getting a copy of Redgate SQL Compare. When I was a full-time DBA, I had that open on my screen all day, most days.
DISCLAIMER: I work for Redgate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2019 at 8:09 am
itmasterw 60042 - Wednesday, January 9, 2019 1:21 PMHi,
I have two tables which are very similar, and they would like to make them one table. However, before I can do that I need a way to compare each column name and see what names are in one table and not the other. these two tables are large so, while it could be it would be hard if I have to compare name by name in a list; so some kind of query would be helpful here.
Also, it would be good if I could get things like the data type/size for each field.
Thank you
I think that someone needs to look at possible normalization of the table before they combine two wide table into a wider table. There's a shedload of pitfalls not only in having a denormalized table but having a wide denormalized table not the least of which is query performance and index maintenance.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply