Compare DataBases......

Hi

I want Tom Compare two databases to see if there are stroed procedure \ table that exist in one database

but not exist in the other.....

how do i do it?

Thanks

hi,

Please find the solution below:

Code Snippet

--SQL 2005

select [name] from <database name>..sysobjects

where type = 'P'

except

select [name] from <database name>..sysobjects

where type = 'P'

--SQL 2000

select [name] from <database name>..sysobjects

where type = 'P'

and [name] not in (

select [name] from <database name>..sysobjects

where type = 'P')

|||

There are several good third party products that will compare databases, and provide you with scripts to make them the same (as well as reports). These products will not only compare the object names, but will also compare the object definitions to see if they are the same.

I suggest that you explore Red Gate's SQL Compare, or ApexSQL's SQL Diff products. Visual Studio for Database Professionals also has that capability. (Most have a 14-30 day fully featured evaluation version so that you can try them out.)

Comparison Tools
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare
DTS Comparison
Red Gate – DTS Package Compare
Server Comparison
Quest - ServerCompare
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition

|||

Lots of ways to do this as mentioned above. Also, the Visual Studio Team Edition for Database Professionals has this built in. you can also code your own:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=108&rl=1