[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Compare two Databases?

Posted on 2010-01-01
10
Medium Priority
?
295 Views
Last Modified: 2012-05-08
How can I compare two Table/Procedures/Functions of two Databases on same server. I don't need byte comparison, only that they are of same date (update date)?
0
Comment
Question by:Jess31
10 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26158153
If You want to compare Scripts of your Tables/ Procedures / Functions then you can compare text column in both databases..

Compared to this approach using a third party tools like

1. Apex SQL Diff (http://www.apexsql.com/sql_tools_diff.asp)
2. Red Gate Compare (http://www.red-gate.com/products/SQL_Compare/index.htm)
0
 
LVL 4

Expert Comment

by:James_Jnr
ID: 26158519
If I understand your question correctly you just want to compare the Creation or modified date of the stored procedure/table etc..

The SQL below will give you want you want, if you look up the details on sys.objects on msdn you can alter this to get you the results you want;

--Stored procedures

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'

--Tables

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'TT'

view the options here;

http://msdn.microsoft.com/en-us/library/ms190324.aspx


0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26159544
Typo.. This "you can compare text column in both databases.." should read as "compare text columns in sys.syscomments table in both databases"

More info about syscomments table here:

http://msdn.microsoft.com/en-us/library/ms186293.aspx
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 26159857
If both databases ( coded below as <database1> and <database2> you need to replace those with the correct name) are on the same server, then fine, otherwise might need to make a linked server to the other server.

Now assume that one of them is the "driver" database - in which case you might like to consider an outer join just in case the new tables or procedures or function are not in <database2> yet. Also added in views to check as well...

Anyway, have a look at :

select db1.type,db1.type_desc,db1.name, db1.create_date,db1.modify_date,
       db2.type,db2.type_desc,db2.name, db2.create_date,db2.modify_date
from <database1>.sys.objects db1
inner join <database2>.sys.objects db2 on db2.name = db1.name and db2.type = db1.type
where db1.type in ('U','P','FN','V')    
and db1.modify_date <> db2.modify_date

for SQL2005 the sys.objects definition is : http://msdn.microsoft.com/en-us/library/ms190324(SQL.90).aspx


0
 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 26161245
0
 
LVL 5

Expert Comment

by:dbidba
ID: 26168839
TOAD for SQL Server also does a nice job of comparing databases. It will compare two different databases to each other and also compare the same database to itself at a different point in time. Very useful to find out what changed when something breaks.
0
 
LVL 5

Expert Comment

by:sureshchsahu
ID: 26168853
Look for the below link, it will help you.

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
0
 
LVL 3

Accepted Solution

by:
uma_ earned 2000 total points
ID: 26168879
Give a try to

www.comparesql.com/

0
 
LVL 15

Expert Comment

by:Faiga Diegel
ID: 26172051
I use Red Gates's SQL Compare tool for couple of years now, it works fine -- atleast from what I want and need :)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26346641
>> I don't need byte comparison, only that they are of same date (update date)?

Look like you wanted more than that after all...

Had you said so to begin with then there are free tools available.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question