March 2, 2011 at 9:33 am
how can i compare two tables in two different databases within the same server.
March 2, 2011 at 9:35 am
SELECT * FROM master.sys.objects
EXCEPT
SELECT * FROM msdb.sys.objects
March 2, 2011 at 9:37 am
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
March 2, 2011 at 9:45 am
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
March 2, 2011 at 9:49 am
Use my code. You'll just have to list all the columns you need compared in both queries instead of select *.
March 2, 2011 at 10:03 am
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
March 2, 2011 at 10:11 am
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.
March 2, 2011 at 10:48 am
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
April 1, 2016 at 5:41 am
This can be achieved using a very inexpensive tool from around $20 US. SQLC[/url] 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.
April 1, 2016 at 5:55 am
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
April 1, 2016 at 6:03 am
I'm a fan of the Redgate SQL Comparison tools. They've helped me a few times in the past with tricky situations.
Do you have Visual Studio? I've got the 2012 Version and this has a data comparison utility.
April 1, 2016 at 6:54 am
April 5, 2016 at 2:31 pm
MS provides tablediff.exe for free. It works ... but it is a command-line tool and, like bcp, takes a bit of time/work to get used to and get it working. And it, too, can generate a sync script.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 5, 2016 at 2:49 pm
For the structural differences, I'd just use SQL. For the data, look at simplicity and performance of different tools and pick the one you want. The alternative is to write an approach that uses SQL. The EXCEPT operator is pretty useful for this and is an efficient approach.
SELECT ID, Name FROM FirstDB.dbo.TableName
EXCEPT
SELECT ID, Name FROM SecondDB.dboTableName;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply