|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Comparing databasesHi,
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 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 > > 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 > > > > > > 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 >> > >> > >> >> > > 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. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "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 >> > >> > >> >> > > > Now I need to go a level deeper, by comparing missing & different columns in Try this> tables. Is it possible to get the results from the system tables or do I > have to use DMO? 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.
Other interesting topics
Help! Users can't connect to database
Help with stored procedure using "IN" SQL2005: "Registered Owner" and "Registered Organization" available? which query is more efficent? (oppinion time!) Use of SELECT...,0 FROM Stored Procedure Help Dynamically use variables in SQL in EXECUTE Need help with this Select all Rows on distinct only on one column Sql Server - TRN Log shrink or truncate. |
|||||||||||||||||||||||