Comparing databases

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,
IvanIvan
Visit at http://www.red-gate.com
"Ivan Debono" <ivanmdeb@.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
>|||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" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> Ivan
> Visit at http://www.red-gate.com
>
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
etc.
columns
>|||Well , then I'd use DMO objects library
"Ivan Debono" <ivanmdeb@.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" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||What to use depends on whether you prefer to work at the TSQL level or at th
e API level:
TSQL: For 2000, use syscolumns. For 2005, use sys.columns. Or (either versio
n) 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/
"Ivan Debono" <ivanmdeb@.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 m
y
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||
> 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.