Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Comparing two tables in two databases Expand / Collapse
Author
Message
Posted Wednesday, March 2, 2011 9:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 4, 2015 11:48 AM
Points: 166, Visits: 531
how can i compare two tables in two different databases within the same server.
Post #1072025
Posted Wednesday, March 2, 2011 9:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, April 13, 2016 12:23 AM
Points: 20,643, Visits: 9,671
SELECT * FROM master.sys.objects
EXCEPT
SELECT * FROM msdb.sys.objects
Post #1072026
Posted Wednesday, March 2, 2011 9:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, Visits: 644
A little more information would help


how many columns to compare

1 or 2 just join the to tables using database_name.dbo.table

if it is a lot redgate has a couple of tools that do it pretty good



Post #1072028
Posted Wednesday, March 2, 2011 9:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 4, 2015 11:48 AM
Points: 166, Visits: 531
I am sorry i should have had more details. I was trying to compare data and columns in two DB's where all the tables are identical but some tables in the second db has more columns in some columns, and data associated with that column, please let me know if i can do that without a tool
Post #1072035
Posted Wednesday, March 2, 2011 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, April 13, 2016 12:23 AM
Points: 20,643, Visits: 9,671
Use my code. You'll just have to list all the columns you need compared in both queries instead of select *.
Post #1072038
Posted Wednesday, March 2, 2011 10:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 4, 2015 11:48 AM
Points: 166, Visits: 531
the script compares all the databases can i compare just two when i tried to replace the scripts as

SELECT AREA FROM [dbo.A]
EXCEPT
SELECT AREA FROM [dbo.B]

I get an error as

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.A'.

So i am doing something wrong, what i donot know

Post #1072044
Posted Wednesday, March 2, 2011 10:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, April 13, 2016 12:23 AM
Points: 20,643, Visits: 9,671
Look at my code again. It's select * from dbname.owner.tblname

You need all 3. And if you need to use brackets then you need to close the brackets before each period.
Post #1072047
Posted Wednesday, March 2, 2011 10:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,853, Visits: 10,198
SQLTestUser (3/2/2011)
I am sorry i should have had more details. I was trying to compare data and columns in two DB's where all the tables are identical but some tables in the second db has more columns in some columns, and data associated with that column, please let me know if i can do that without a tool


This will show you tables with columns that are in db2, but not in db1, or are in db1 in a different order. If you don't care about the order, remove the sc.column id everywhere it's referenced in the query.

Change db1 and db2 to your appropriate database names. The database that you suspect has more columns should be the one before the EXCEPT operator.

SELECT TableName = st.NAME,
ColumnName = sc.NAME,
sc.column_id
FROM [db2].sys.tables st
JOIN [db2].sys.columns sc
ON st.object_id = sc.OBJECT_ID
EXCEPT
SELECT TableName = st.NAME,
ColumnName = sc.NAME,
sc.column_id
FROM [db1].sys.tables st
JOIN [db1].sys.columns sc
ON st.object_id = sc.OBJECT_ID
ORDER BY TableName, column_id

Does this get you what you're looking for?


Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1072078
Posted Friday, April 1, 2016 5:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 8, 2016 7:08 AM
Points: 11, Visits: 12
This can be achieved using a very inexpensive tool from around $20 US. SQLC allows you to compare two tables in two different databases even on two different servers where the table structures are not the same. It lets you choose the columns that are common to both tables and compare on those columns only. Of course it does database object comparison as well. There is even a free trial version with limited functionality but no time limits.
Post #1774168
Posted Friday, April 1, 2016 5:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 5, 2016 1:47 PM
Points: 261, Visits: 557
go with a tool

you can get something like DBDiff free - or spend a little money and get DBGhost, which not only will give you the differences but the script to fix them
OF COURSE for even more money RedGate has some really really slick products
Post #1774178
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse