does anyone have a query to check for db differences

I would like to compare 2 dbs at an object level to ensure that all schema
are the same on the 2 dbs and give me differences with any
I know of 3rd party tools from Red-gate , but was hoping someone had written
a simple script that i could use
Thanks
Hassan
> I know of 3rd party tools from Red-gate , but was hoping someone had
> written
> a simple script that i could use
>
Simple script? Are you kidding? It is very compicated issue. Personally , I
use Red-gate and it does work pretty good
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uilnoVmkFHA.3544@.TK2MSFTNGP15.phx.gbl...
>I would like to compare 2 dbs at an object level to ensure that all schema
> are the same on the 2 dbs and give me differences with any
> I know of 3rd party tools from Red-gate , but was hoping someone had
> written
> a simple script that i could use
> Thanks
>
|||One thing you could try is to script out all the objects from each database
into two separate folders (with the option of a separate file for each
object). And then compare the folders using a tool like WinDiff that comes
with Visual Studio.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uilnoVmkFHA.3544@.TK2MSFTNGP15.phx.gbl...
>I would like to compare 2 dbs at an object level to ensure that all schema
> are the same on the 2 dbs and give me differences with any
> I know of 3rd party tools from Red-gate , but was hoping someone had
> written
> a simple script that i could use
> Thanks
>
|||I mean simple such as
select a.name from dbold..sysobjects a
where not exists (select * from dbnew.dbo.sysobjects b where a.name =b.name)
select a.name from dbold..sysindexes a
where not exists (select * from dbnew.dbo.sysindexes b where a.name =b.name)
Would this be fairly accurate if i want to capture everything ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6fu%237mkFHA.3212@.TK2MSFTNGP10.phx.gbl...
> Hassan
> Simple script? Are you kidding? It is very compicated issue. Personally ,
I[vbcol=seagreen]
> use Red-gate and it does work pretty good
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uilnoVmkFHA.3544@.TK2MSFTNGP15.phx.gbl...
schema
>
|||I wasnt looking at all details such as text of sprocs, triggers, views,etc..
just the names of objects that are not present
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%231TsN$mkFHA.3148@.TK2MSFTNGP09.phx.gbl...
> One thing you could try is to script out all the objects from each
database[vbcol=seagreen]
> into two separate folders (with the option of a separate file for each
> object). And then compare the folders using a tool like WinDiff that comes
> with Visual Studio.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uilnoVmkFHA.3544@.TK2MSFTNGP15.phx.gbl...
schema
>
|||Hi
Well, I'd not query system tables , instead INFORMATION_SCHEMA view_name
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23xxItBnkFHA.2852@.TK2MSFTNGP15.phx.gbl...
>I mean simple such as
> select a.name from dbold..sysobjects a
> where not exists (select * from dbnew.dbo.sysobjects b where a.name
> =b.name)
>
> select a.name from dbold..sysindexes a
> where not exists (select * from dbnew.dbo.sysindexes b where a.name
> =b.name)
> Would this be fairly accurate if i want to capture everything ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O6fu%237mkFHA.3212@.TK2MSFTNGP10.phx.gbl...
> I
> schema
>
|||Yes...you could smply do a left or full outer join between the two databases
to work out which ones are missing on each side.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u9iopHnkFHA.1440@.TK2MSFTNGP14.phx.gbl...
>I wasnt looking at all details such as text of sprocs, triggers,
>views,etc..
> just the names of objects that are not present
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:%231TsN$mkFHA.3148@.TK2MSFTNGP09.phx.gbl...
> database
> schema
>
|||You can still do this by scripting the databases to seperate folders and
comparing them with a Diff tool. I have found that BeyondCompare works well,
but Windiff would work fine too. Whatever is on one side but not the other
is what is missing. I currently do this for a database with 375 tables and
450 Stored Procedures in an environment with 20 developers making changes
daily. The developers are supposed to be checking changes in through Visual
Source Safe, but often forget to do so. I catch the problems before they
become an issue and let the developers know that need to check their changes
in/out of VSS.
"Hassan" wrote:

> I wasnt looking at all details such as text of sprocs, triggers, views,etc..
> just the names of objects that are not present
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:%231TsN$mkFHA.3148@.TK2MSFTNGP09.phx.gbl...
> database
> schema
>
>
|||This tool allows you to view all the db contents al logical level
as well as to query the oledb schemas. Might be of some help (free)
http://151.100.3.84/technicalpreview/