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

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: Wednesday, October 22, 2014 9:59 AM
Points: 161, Visits: 509
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: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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: Wednesday, October 22, 2014 9:59 AM
Points: 161, Visits: 509
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: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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: Wednesday, October 22, 2014 9:59 AM
Points: 161, Visits: 509
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: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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 @ 11:11 AM
Points: 5,366, Visits: 8,981
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
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse