• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Compare two Databases?

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
Jess31
Asked:
Jess31
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
James_JnrCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
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.

 
Mark WillsTopic AdvisorCommented:
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
 
dbidbaCommented:
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
 
sureshchsahuCommented:
Look for the below link, it will help you.

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
0
 
uma_Commented:
Give a try to

www.comparesql.com/

0
 
Faiga DiegelSr Database EngineerCommented:
I use Red Gates's SQL Compare tool for couple of years now, it works fine -- atleast from what I want and need :)
0
 
Mark WillsTopic AdvisorCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now