Comparing two SQLServer databases

Hi, does anybody know a nice tool to automatically compare and show
only the structural differences between two databases, this is, the
differences in the columns of a table, constrains, data type, etc.

Thanks

J.A.I use this one (there are a number of other, simipar products):

http://www.red-gate.com/SQL_Compare.htm

Simon|||For excellent compare functionality (including data) plus the ability
to link your source control system into the whole process I recommend
looking at DB Ghost (www.dbghost.com). It also guarantees any delta
script produced will work (due to a unique algorithm), something no
other tool can promise.

Comparing two databases

Is there a way to compare the strored procedure,views and UDF's between two
databases to see if there are any differences. I use one database for
developement and the other is online. I would like to be able to run a
structural comparison between the two to make sure i didn't forget to script
a function or stored procedure after making modifications. I normally just
script all objects from developement to online after mods but I would like t
o
know for certain they are both the same sometimes. Also I would like to read
up on best practices for tracking developement so if you know of any good
reading that would help me. I use MS Access project as a front end and SQL
2000 as the Be. Thanks> Is there a way to compare the strored procedure,views and UDF's between
> two
> databases to see if there are any differences. I use one database for
> developement and the other is online. I would like to be able to run a
> structural comparison between the two to make sure i didn't forget to
> script
> a function or stored procedure after making modifications.
SQL Compare 4.0
http://www.red-gate.com/|||"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:3A5AF648-8D41-441A-BA91-0EF14B5C09A0@.microsoft.com...
> Is there a way to compare the strored procedure,views and UDF's between tw
o
> databases to see if there are any differences. I use one database for
We use SQL Delta. Does wonders for our spare time! (maintaining/updating app
rox. 25 DB
installations)
http://www.sqldelta.com|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
Rather than scripting all objects from development to production in bulk,
you need to identify specific objects that have changed and deploy them
individually. There are a number of reasons, but for one, you run the risk
of accidentally running a script that drops / recreates a table thus
resulting in data loss.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:3A5AF648-8D41-441A-BA91-0EF14B5C09A0@.microsoft.com...
> Is there a way to compare the strored procedure,views and UDF's between
> two
> databases to see if there are any differences. I use one database for
> developement and the other is online. I would like to be able to run a
> structural comparison between the two to make sure i didn't forget to
> script
> a function or stored procedure after making modifications. I normally just
> script all objects from developement to online after mods but I would like
> to
> know for certain they are both the same sometimes. Also I would like to
> read
> up on best practices for tracking developement so if you know of any good
> reading that would help me. I use MS Access project as a front end and SQL
> 2000 as the Be. Thanks

Comparing table structures.

Hi all,

Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?

Thanks

DBAnalyst

I once made a script for that, feel free to contact me if I should send it over.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Please send it Jens, Thanks a lot.sqlsql

Comparing differences in database structure between databases

I am trying to find a way to easily compare the difference in table/column structure between two SQL server databases.

I am doing this since I need to document the foreign key relationships between a database schema that is currently under development. The foreign keys are not defined as constraints in the database, but are controlled through the application.

The current naming conventions make it easy to see what the relationships are (primary keys are "tablename_seq" and foreign keys use the same names, only tables that have foreign keys that reference themselves break this rule, with a suffix added to the primary key name like "tablename_seq_parent").

In order to document what these relationships are, I have created a copy of the database and set up the foreign key restraints so that I can use the database digram tools in SQL Server Management Studio, or Visio. It took quite a bit of manual work to create all these relationships.

Now the developers have added new tables, or made changes to tables and I need to keep the document up to date. Manually keeping track of all the changes will probably be an issue so I am looking for either:

a way of automatically generating an update script for my database when comparing to the development database, so that I can update to the latest version (then manually create the new constraints OR a way of automatically reading in information on the tables from a database and creating foreign key relationships for any primary key that is a column of another table (ie. has the same name)

The closest thing I have found that might help solve the first option is the tablediff utility. I thought perhaps I could write a script (it has been a while!!) that:

Lists all tables in the developer database For each table check if it exists in my database If it does exist then use tablediff to check for new or changed columns and generate a script to change the table using -f. If it doesn't exist then create table using script If tables exist in the destination database but not in the main one then flag them for followup manually.

Does anyone know of a simpler way that I have missed?

Regards

Jo

Hi all

Does anyone have any ideas on this? Or have I posted to the wrong forum?

Regards

Jo

|||

Hi,

Easiest thing to do is to get a third-party tool for this. I personally use the SQL Tools from Red-Gate.

For what you want , its SQL Compare. Its 295 USD, and a total bargain. There are others, from ApexSQL, and DB Ghost...

However, I use all Red-Gate tools regularly and wouldn't be without them and for me, I plumped for the SQL Bundle Pro (990 USD).

Cheers.

Paul

|||

I use a product called AdeptSQL Diff, it's pretty quick to scan my databases (6500 procs and 1100 tables in about a minute) and easy to use. www.adeptsql.com

The comparison tool is 240 USD and if you want to compare your data as well, it's 320 USD.

Jarret

|||If nothing else, version your DB objects as scripts within VSS, and do a compare between script versions.

Comparing DBs with Windiff

I scripted two versions of a database and then compared the text files using
WinDiff. There were major differences in the two files but Windiff said
they were the same "except for blanks." Has anyone else had such a problem
with WinDiff?
Are there any good (free or cheap) file comparison programs around that
work?
Thanks,
G
GaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
Have you tried the command-line "fc.exe" that is bult into Windows. It
should be in the path, but on my XP system is located in the
WINDOWS\SYSTEM32 folder.
David Gugick
Imceda Software
www.imceda.com
|||Not with Windiff, but I've observed the problem with RedGate SQL Compare
where database objects show as the same but manual examination reveals
significant differences.
Michael D. Long
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
>I scripted two versions of a database and then compared the text files
>using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
> problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>
|||Would you be able to post some segments of text that aren't comparing
properly? I'm very surprised -- and disturbed -- to hear that Windiff isn't
reliable; it's quite an old product at this point, and I use it extensively
to compare source code. So I'd really like to know if it has deficiencies.
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
> I scripted two versions of a database and then compared the text files
using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>
|||GaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
I think the problem is that WinDiff is not designed for use with binary
files. It's an ASCII comparison tool. I would use fc.exe instead.
See this article:
http://support.microsoft.com/default...b;en-us;159214
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
Last time I checked, SQL Scripts are just text files. But I guess the
OP could have had them Unicode encoded?
Anyway, if you want a better diff/merge program than WinDiff, I highly
recommend Beyond Compare... fc.exe is rather ancient and difficult to use in
this age of Graphical User Interfaces...
http://www.scootersoftware.com/moreinfo.html
|||Adam Machanic wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Last time I checked, SQL Scripts are just text files. But I
> guess the OP could have had them Unicode encoded?
> Anyway, if you want a better diff/merge program than WinDiff, I
> highly recommend Beyond Compare... fc.exe is rather ancient and
> difficult to use in this age of Graphical User Interfaces...
> http://www.scootersoftware.com/moreinfo.html
I misread the OP. I thought he was comparing data files.
On another note, I use Beyond Compare as well and think it's a great
tool although I do find myself using FC.EXE on occasion for quick
comparisons.
David Gugick
Imceda Software
www.imceda.com
|||..sql files are text script files.
G
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> GaryB wrote:
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
> See this article:
> http://support.microsoft.com/default...b;en-us;159214
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Sorry but it is a script of our entire database that is propritary. one
data base had many tables missing, views missing, some column differences,
and stored procedures missing. WinDiff just pops up a dialog saying
difference in blanks only. I'm using WinDiff 5.1. I'll try tht Beyond
Compare.
G
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uxnwyoEoEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Would you be able to post some segments of text that aren't comparing
> properly? I'm very surprised -- and disturbed -- to hear that Windiff
> isn't
> reliable; it's quite an old product at this point, and I use it
> extensively
> to compare source code. So I'd really like to know if it has
> deficiencies.
>
> "GaryB" <gb@.nospam.com> wrote in message
> news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
> using
> problem
>
sqlsql

Comparing DBs with Windiff

I scripted two versions of a database and then compared the text files using
WinDiff. There were major differences in the two files but Windiff said
they were the same "except for blanks." Has anyone else had such a problem
with WinDiff?
Are there any good (free or cheap) file comparison programs around that
work?
Thanks,
GGaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
Have you tried the command-line "fc.exe" that is bult into Windows. It
should be in the path, but on my XP system is located in the
WINDOWS\SYSTEM32 folder.
--
David Gugick
Imceda Software
www.imceda.com|||Not with Windiff, but I've observed the problem with RedGate SQL Compare
where database objects show as the same but manual examination reveals
significant differences.
--
Michael D. Long
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
>I scripted two versions of a database and then compared the text files
>using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
> problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>|||Would you be able to post some segments of text that aren't comparing
properly? I'm very surprised -- and disturbed -- to hear that Windiff isn't
reliable; it's quite an old product at this point, and I use it extensively
to compare source code. So I'd really like to know if it has deficiencies.
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
> I scripted two versions of a database and then compared the text files
using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>|||GaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
I think the problem is that WinDiff is not designed for use with binary
files. It's an ASCII comparison tool. I would use fc.exe instead.
See this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;159214
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
Last time I checked, SQL Scripts are just text files. But I guess the
OP could have had them Unicode encoded?
Anyway, if you want a better diff/merge program than WinDiff, I highly
recommend Beyond Compare... fc.exe is rather ancient and difficult to use in
this age of Graphical User Interfaces...
http://www.scootersoftware.com/moreinfo.html|||For tools, I would check out Innovartis DB Ghost
http://www.innovartis.co.uk/
Regards,
Nick Evans
>--Original Message--
>I scripted two versions of a database and then compared
the text files using
>WinDiff. There were major differences in the two files
but Windiff said
>they were the same "except for blanks." Has anyone else
had such a problem
>with WinDiff?
>Are there any good (free or cheap) file comparison
programs around that
>work?
>Thanks,
>G
>
>.
>|||Adam Machanic wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
>> I think the problem is that WinDiff is not designed for use with
>> binary files. It's an ASCII comparison tool. I would use fc.exe
>> instead.
> Last time I checked, SQL Scripts are just text files. But I
> guess the OP could have had them Unicode encoded?
> Anyway, if you want a better diff/merge program than WinDiff, I
> highly recommend Beyond Compare... fc.exe is rather ancient and
> difficult to use in this age of Graphical User Interfaces...
> http://www.scootersoftware.com/moreinfo.html
I misread the OP. I thought he was comparing data files.
On another note, I use Beyond Compare as well and think it's a great
tool although I do find myself using FC.EXE on occasion for quick
comparisons.
David Gugick
Imceda Software
www.imceda.com|||.sql files are text script files.
G
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> GaryB wrote:
>> I scripted two versions of a database and then compared the text
>> files using WinDiff. There were major differences in the two files
>> but Windiff said they were the same "except for blanks." Has anyone
>> else had such a problem with WinDiff?
>> Are there any good (free or cheap) file comparison programs around
>> that work?
>> Thanks,
>> G
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
> See this article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;159214
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Sorry but it is a script of our entire database that is propritary. one
data base had many tables missing, views missing, some column differences,
and stored procedures missing. WinDiff just pops up a dialog saying
difference in blanks only. I'm using WinDiff 5.1. I'll try tht Beyond
Compare.
G
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uxnwyoEoEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Would you be able to post some segments of text that aren't comparing
> properly? I'm very surprised -- and disturbed -- to hear that Windiff
> isn't
> reliable; it's quite an old product at this point, and I use it
> extensively
> to compare source code. So I'd really like to know if it has
> deficiencies.
>
> "GaryB" <gb@.nospam.com> wrote in message
> news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
>> I scripted two versions of a database and then compared the text files
> using
>> WinDiff. There were major differences in the two files but Windiff said
>> they were the same "except for blanks." Has anyone else had such a
> problem
>> with WinDiff?
>> Are there any good (free or cheap) file comparison programs around that
>> work?
>> Thanks,
>> G
>>
>

Comparing Databases

Hello,
I would like write a tool which will compare two databases
and check for differences, so can someone well me
1. Where are the database names stored
2. Where the column names are stored
3. Where the code for the views is stored
4. Where the code for the Store Procedures is stored
5. Where the code for the functions, defaults ect.
I would rather not use INFORMATION_SCHEMA views as they
look to be very database specific.
I know there are tools to do this but my company isn't
buying, and yes I have told them how much they will save
if we buy rather than make ;)
PeterCheck out:
http://www.sql-server-performance.c...mparison_sp.asp
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:954101c3eb05$dc090b90$a101280a@.phx.gbl...

quote:

---

> Hello,
> I would like write a tool which will compare two databases
> and check for differences, so can someone well me
> 1. Where are the database names stored
> 2. Where the column names are stored
> 3. Where the code for the views is stored
> 4. Where the code for the Store Procedures is stored
> 5. Where the code for the functions, defaults ect.
> I would rather not use INFORMATION_SCHEMA views as they
> look to be very database specific.
> I know there are tools to do this but my company isn't
> buying, and yes I have told them how much they will save
> if we buy rather than make ;)
> Peter

---

|||Thanks SriSamp thats perfect
Peter

quote:

---

>--Original Message--
>Check out:
>http://www.sql-server-

---

performance.com/vg_database_comparison_sp.asp

quote:

---

>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Peter" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:954101c3eb05$dc090b90$a101280a@.phx.gbl...
databases[QUOTE]
>
>.
>

---

Comparing databases

Hello,

I would like to make a comparison between two databases in SQL server,
to find differences between tables, procedures and so on.

I have searched the web and found several softwares able to perform the
task, but none able to compare a database with a backup otained from a
SQL Server database, or between two backups.

Is this possible ? Is there a software able to perform such comparison
?

Thank you for your help.

--
L'amour, c'est un sport. Surtout s'il y en a un des deux qui veut pas.
[Jean Yanne]"ByB" <email@.email.comwrote in message
news:mn.957d7d7999326e87.13846@.email.com...

Quote:

Originally Posted by

Hello,
>
I would like to make a comparison between two databases in SQL server, to
find differences between tables, procedures and so on.
>
I have searched the web and found several softwares able to perform the
task, but none able to compare a database with a backup otained from a SQL
Server database, or between two backups.
>
Is this possible ? Is there a software able to perform such comparison ?
>
Thank you for your help.
>
--
L'amour, c'est un sport. Surtout s'il y en a un des deux qui veut pas.
[Jean Yanne]
>
>

I don't think you'll find any tool that can compare a database to the
contents of a backup file.

Check out Red Gate for database to database comparison.

http://www.red-gate.com/products/SQL_Compare/index.htm
http://www.red-gate.com/products/SQ...mpare/index.htm
--
David Portas|||On Sep 18, 5:25 pm, ByB <em...@.email.comwrote:

Quote:

Originally Posted by

Hello,
>
I would like to make a comparison between two databases in SQL server,
to find differences between tables, procedures and so on.
>
I have searched the web and found several softwares able to perform the
task, but none able to compare a database with a backup otained from a
SQL Server database, or between two backups.
>
Is this possible ? Is there a software able to perform such comparison
?
>
Thank you for your help.
>
--
L'amour, c'est un sport. Surtout s'il y en a un des deux qui veut pas.
[Jean Yanne]

ApexSQL is very good.|||Another great tool for comparing and synchronising SQL Server
databases (schema and data) is DB Ghost from Innovartis. It can also
let you manage your "source" database as a set of CREATE scripts under
source control:

http://www.dbghost.com
http://www.innovartis.co.uk|||On Sep 18, 11:29 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

"ByB" <em...@.email.comwrote in message
>
news:mn.957d7d7999326e87.13846@.email.com...
>
>
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

I would like to make a comparison between two databases in SQL server, to
find differences between tables, procedures and so on.

>

Quote:

Originally Posted by

I have searched the web and found several softwares able to perform the
task, but none able to compare a database with a backup otained from a SQL
Server database, or between two backups.

>

Quote:

Originally Posted by

Is this possible ? Is there a software able to perform such comparison ?

>

Quote:

Originally Posted by

Thank you for your help.

>

Quote:

Originally Posted by

--
L'amour, c'est un sport. Surtout s'il y en a un des deux qui veut pas.
[Jean Yanne]

>
I don't think you'll find any tool that can compare a database to the
contents of a backup file.
>
Check outRed Gatefor database to database comparison.
>
http://www.red-gate.com/products/SQ...mpare/index.htm
>
--
David Portas

Coincidentally, comparing to a backup is one of the new features in
SQL Data Compare 6 Pro, which is due out very shortly. If you would
like to try this out, the Alpha release should still be available from
our forum page: http://www.red-gate.com/messageboard/index.php
Most likely we will be releasing a public release candidate sometime
next week, so check the SQL Data Compare forums for announcements.

Kind regards,

David Atkinson
Red Gate Softwaresqlsql

Comparing Databases

Hello,
I would like write a tool which will compare two databases
and check for differences, so can someone well me
1. Where are the database names stored
2. Where the column names are stored
3. Where the code for the views is stored
4. Where the code for the Store Procedures is stored
5. Where the code for the functions, defaults ect.
I would rather not use INFORMATION_SCHEMA views as they
look to be very database specific.
I know there are tools to do this but my company isn't
buying, and yes I have told them how much they will save
if we buy rather than make ;)
PeterCheck out:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:954101c3eb05$dc090b90$a101280a@.phx.gbl...
> Hello,
> I would like write a tool which will compare two databases
> and check for differences, so can someone well me
> 1. Where are the database names stored
> 2. Where the column names are stored
> 3. Where the code for the views is stored
> 4. Where the code for the Store Procedures is stored
> 5. Where the code for the functions, defaults ect.
> I would rather not use INFORMATION_SCHEMA views as they
> look to be very database specific.
> I know there are tools to do this but my company isn't
> buying, and yes I have told them how much they will save
> if we buy rather than make ;)
> Peter|||Thanks SriSamp thats perfect
Peter
>--Original Message--
>Check out:
>http://www.sql-server-
performance.com/vg_database_comparison_sp.asp
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:954101c3eb05$dc090b90$a101280a@.phx.gbl...
>> Hello,
>> I would like write a tool which will compare two
databases
>> and check for differences, so can someone well me
>> 1. Where are the database names stored
>> 2. Where the column names are stored
>> 3. Where the code for the views is stored
>> 4. Where the code for the Store Procedures is stored
>> 5. Where the code for the functions, defaults ect.
>> I would rather not use INFORMATION_SCHEMA views as they
>> look to be very database specific.
>> I know there are tools to do this but my company isn't
>> buying, and yes I have told them how much they will save
>> if we buy rather than make ;)
>> Peter
>
>.
>

Comparing 2 dbs for differences in column definitions

Hi All
I have 2 dbs, one for prodcution and the other one for development
environment. They are meant to be the same.
Is there a script/way to compare the dbs for any difference in column
datatypes, length.
This query has what i want to compare:
select
table_name, column_name, data_type, character_maximum_length [Length],
is_nullable [Null], numeric_precision NuPrec, numeric_scale NuScal,
datetime_precision DTPrec
from
information_schema.columns
order by table_name ASC, column_name ASC
Thank you on advance.Maybe you should take a look at Red Gate's SQL Compare.
http://www.red-gate.com/
ML
http://milambda.blogspot.com/|||See replies to the post "Comparing two databases".
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:844E5968-C506-4A25-A410-2128BA6F2741@.microsoft.com...
> Hi All
> I have 2 dbs, one for prodcution and the other one for development
> environment. They are meant to be the same.
> Is there a script/way to compare the dbs for any difference in column
> datatypes, length.
> This query has what i want to compare:
> select
> table_name, column_name, data_type, character_maximum_length [Length],
> is_nullable [Null], numeric_precision NuPrec, numeric_scale NuScal,
> datetime_precision DTPrec
> from
> information_schema.columns
> order by table_name ASC, column_name ASC
> Thank you on advance.
>

Compare Differences 2 Fields

I have an Id# as a PK in one table and I am trying to create a FK relationship on another table I need to compare the data because the new FK table has a few Id#'s that don't match up what is the best way to do this?-- this will list the ID in onetable but not in anothertable
select *
from onetable o left join anothertable a
on o.id = a.id
where a.id is null

do you have the other case ? ID in anothertable but not in onetable ?|||

Maybe something like:

select id#
from newTable a
where not exists
( select id#
from firstTable b
where a.id# = b.id#
)