Comparing databases

8 Jun 2006 6:39 AM

Ivan Debono

Hi,

I need to compare 2 databases to check for missing objects, columns, etc.
Comparing objects was pretty easy. Just a pair of sql statements on the
sysobjects table and it worked fine.

Now I need to go a level deeper, by comparing missing & different columns in
tables. Is it possible to get the results from the system tables or do I
have to use DMO?

Thanks,
Ivan

8 Jun 2006 6:47 AM

Uri Dimant

Ivan
Visit at http://www.red-gate.com

Show quoteHide quote

"Ivan Debono" <ivanm***@hotmail.com> wrote in message
news:OywqAZsiGHA.1508@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I need to compare 2 databases to check for missing objects, columns, etc.
> Comparing objects was pretty easy. Just a pair of sql statements on the
> sysobjects table and it worked fine.
>
> Now I need to go a level deeper, by comparing missing & different columns
> in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
>
> Thanks,
> Ivan
>
>

8 Jun 2006 7:31 AM

Ivan Debono

I know that there are quite a few tools that exist, but I need to develop my
own tool as this will be part of yet another bigger suite of tools.

Show quoteHide quote

"Uri Dimant" <u***@iscar.co.il> schrieb im Newsbeitrag
news:u9YYEdsiGHA.3884@TK2MSFTNGP04.phx.gbl...
> Ivan
> Visit at http://www.red-gate.com
>
>
>
>
> "Ivan Debono" <ivanm***@hotmail.com> wrote in message
> news:OywqAZsiGHA.1508@TK2MSFTNGP04.phx.gbl...
> > Hi,
> >
> > I need to compare 2 databases to check for missing objects, columns,
etc.
> > Comparing objects was pretty easy. Just a pair of sql statements on the
> > sysobjects table and it worked fine.
> >
> > Now I need to go a level deeper, by comparing missing & different
columns
> > in
> > tables. Is it possible to get the results from the system tables or do I
> > have to use DMO?
> >
> > Thanks,
> > Ivan
> >
> >
>
>

8 Jun 2006 8:17 AM

Uri Dimant

Well , then I'd use DMO objects library

Show quoteHide quote

"Ivan Debono" <ivanm***@hotmail.com> wrote in message
news:%231DU91siGHA.3780@TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop
>my
> own tool as this will be part of yet another bigger suite of tools.
>
>
> "Uri Dimant" <u***@iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@TK2MSFTNGP04.phx.gbl...
>> Ivan
>> Visit at http://www.red-gate.com
>>
>>
>>
>>
>> "Ivan Debono" <ivanm***@hotmail.com> wrote in message
>> news:OywqAZsiGHA.1508@TK2MSFTNGP04.phx.gbl...
>> > Hi,
>> >
>> > I need to compare 2 databases to check for missing objects, columns,
> etc.
>> > Comparing objects was pretty easy. Just a pair of sql statements on the
>> > sysobjects table and it worked fine.
>> >
>> > Now I need to go a level deeper, by comparing missing & different
> columns
>> > in
>> > tables. Is it possible to get the results from the system tables or do
>> > I
>> > have to use DMO?
>> >
>> > Thanks,
>> > Ivan
>> >
>> >
>>
>>
>
>

8 Jun 2006 9:49 AM

Tibor Karaszi

What to use depends on whether you prefer to work at the TSQL level or at the API level:

TSQL: For 2000, use syscolumns. For 2005, use sys.columns. Or (either version) use the
information_schema views.

API: For 2000, use DMO. For 2005, use SMO.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"Ivan Debono" <ivanm***@hotmail.com> wrote in message
news:%231DU91siGHA.3780@TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop my
> own tool as this will be part of yet another bigger suite of tools.
>
>
> "Uri Dimant" <u***@iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@TK2MSFTNGP04.phx.gbl...
>> Ivan
>> Visit at http://www.red-gate.com
>>
>>
>>
>>
>> "Ivan Debono" <ivanm***@hotmail.com> wrote in message
>> news:OywqAZsiGHA.1508@TK2MSFTNGP04.phx.gbl...
>> > Hi,
>> >
>> > I need to compare 2 databases to check for missing objects, columns,
> etc.
>> > Comparing objects was pretty easy. Just a pair of sql statements on the
>> > sysobjects table and it worked fine.
>> >
>> > Now I need to go a level deeper, by comparing missing & different
> columns
>> > in
>> > tables. Is it possible to get the results from the system tables or do I
>> > have to use DMO?
>> >
>> > Thanks,
>> > Ivan
>> >
>> >
>>
>>
>
>

8 Jun 2006 10:51 AM

MUKUT

> Now I need to go a level deeper, by comparing missing & different columns in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?

Try this

select name from <DB1>..syscolumns where
id=object_id('<DB1>..<TABLE_NAME>') and name not in
(select name from <DB2>..syscolumns where
id=object_id('<DB2>..<TABLE_NAME>'))

--This will return the additional columns in table in another database.

You can well modify it to meet your specefic requirement.