Database Table Compare - SQL SERVER
I need to do a (Table to Table) compare in two different databases on the same server.
I need to find the differences between any given set of two tables with the same name in two different databases. I can not use a third party tool, need a script based solution possibly from the DMVs sysobjects, syscolumns etc....
I need to find the differences between any given set of two tables with the same name in two different databases. I can not use a third party tool, need a script based solution possibly from the DMVs sysobjects, syscolumns etc....
ASKER
Thanks Norman, However, I need a Schema Compare not a Data Compare. Need to see if Columns are missing or if the Data Types are different when they should all be the same.
This code will list you the columns, types, etc. in a table.
select column_name=syscolumns.nam
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.
where sysobjects.name = YOUR TABLE NAME HERE'
and sysobjects.xtype='U'
ORDER BY syscolumns.name
select column_name=syscolumns.nam
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.
where sysobjects.name = YOUR TABLE NAME HERE'
and sysobjects.xtype='U'
ORDER BY syscolumns.name
If you are a developer and have access to VS 2011 ,you can use it to compare and sync schemas.
http://msdn.microsoft.com/en-us/library/dd193250.aspx
Or you can use this free tool:
http://www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html
http://msdn.microsoft.com/en-us/library/dd193250.aspx
Or you can use this free tool:
http://www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html
Use sql compare by Redgate. A very helfpul tool.
Does that give you what you need, or would you prefer an actual script, as orig stated?
ASKER
An actual script is preferred since I do not have access to any of the mainstream tools.
Do you need more details than in than the script I provided above? You can easily create a tablevar with two lots of fields and fill one lot with the results from one run (on one table) of this code and the other lot with the results from another run (on a different table) of this code. Then select all from the tablevar to see differences immediately
Working on a full script now, please just give me a little time :-) .
Go for it Scott! I'm actually in work, so don't have the time for that :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Scott; I tested the script and it provides only the differences. How could this be modified to include the differences plus the rest of the columns in the tables?
Hmm, it should list ALL columns from BOTH tables.
Hmm, wait, to be safe, change the INNER JOINs to LEFT OUTER JOINs. Sorry about that :-) .
It just has columns that tell you if there were differences or not.
Hmm, wait, to be safe, change the INNER JOINs to LEFT OUTER JOINs. Sorry about that :-) .
It just has columns that tell you if there were differences or not.
http://msdn.microsoft.com/en-us/library/ms162843.aspx