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 DBs

is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
GaryThere are quite a few companies that make such products. Check out this
list: http://vyaskn.tripod.com/thirdparty.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gary" <gb@.nospam.com> wrote in message
news:ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl...
is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
Gary|||We use the following tool
http://www.adeptsql.com/
Its cheap, very accurate, very fast and will also update
your out of sync database if you wish. There is also a
trial version which you can download.
J
>--Original Message--
>is there any utility or program available that will
compare two versions of
>the same SQL Server database in terms of definition and
objects. I don't
>want to compare data. I need to see the differences in
tables columns views
>etc.
>Thanks,
>Gary
>
>.
>|||We use ERWin by Computer Associates.
a bit spendy but it does More then just compare Databases.
http://www3.ca.com/Solutions/Product.asp?ID=260
Gregory A Jackson
PDX, Oregon|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
For your question of compare the two versions of the same SQL Server
database in terms of definitions and objects but not data in them. Besides
the utiltities suggested by our MVPs and community members, if the compare
is quite simple, you can just generate a 'script' that define the objects
in 2 SQL Server respectively and compare the script. To generate the
script, they can use SQL Enterprise Manager and choose the "Object|Generate
SQL Script..." menu item. Another way is to use 'sp_help' upon each object
in the 2 database and compare them.
Hope this helps. If you still have questions on this issue, please feel
free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Gary,
As said in the answers, you can use tools to compare the databases.
I prefer to use the sysobjects table to do that, simply by doing
a
SELECT * from db1.sysobjects where not in (SELECT * from db2.sysobjects)
or
SELECT * from db2.sysobjects where not in (SELECT * from db1.sysobjects)
This works if you want to analyse the difference in term of tables
and fields.
To have a deeper analysis (fields of different types) it is more difficult
but possible with other system tables like syscolumns.
Gilles
"Gary" <gb@.nospam.com> wrote in message news:<ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl>...
> is there any utility or program available that will compare two versions of
> the same SQL Server database in terms of definition and objects. I don't
> want to compare data. I need to see the differences in tables columns views
> etc.
> Thanks,
> Gary|||also check out DB Ghost @. www.dbghost.com
regards,
Mark Baekdal
>--Original Message--
>is there any utility or program available that will
compare two versions of
>the same SQL Server database in terms of definition and
objects. I don't
>want to compare data. I need to see the differences in
tables columns views
>etc.
>Thanks,
>Gary
>
>.
>|||I downloaded it and tried it out. Works great. Many thanks.
Gary
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0d0601c3d9fa$103b29b0$a601280a@.phx.gbl...
> We use the following tool
> http://www.adeptsql.com/
> Its cheap, very accurate, very fast and will also update
> your out of sync database if you wish. There is also a
> trial version which you can download.
> J
>
> >--Original Message--
> >is there any utility or program available that will
> compare two versions of
> >the same SQL Server database in terms of definition and
> objects. I don't
> >want to compare data. I need to see the differences in
> tables columns views
> >etc.
> >Thanks,
> >Gary
> >
> >
> >.
> >

Comparing DBs

is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
GaryThere are quite a few companies that make such products. Check out this
list: http://vyaskn.tripod.com/thirdparty.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gary" <gb@.nospam.com> wrote in message
news:ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl...
is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
Gary|||We use ERWin by Computer Associates.
a bit spendy but it does More then just compare Databases.
http://www3.ca.com/Solutions/Product.asp?ID=260
Gregory A Jackson
PDX, Oregon|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
For your question of compare the two versions of the same SQL Server
database in terms of definitions and objects but not data in them. Besides
the utiltities suggested by our MVPs and community members, if the compare
is quite simple, you can just generate a 'script' that define the objects
in 2 SQL Server respectively and compare the script. To generate the
script, they can use SQL Enterprise Manager and choose the "Object|Generate
SQL Script..." menu item. Another way is to use 'sp_help' upon each object
in the 2 database and compare them.
Hope this helps. If you still have questions on this issue, please feel
free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Gary,
As said in the answers, you can use tools to compare the databases.
I prefer to use the sysobjects table to do that, simply by doing
a
SELECT * from db1.sysobjects where not in (SELECT * from db2.sysobjects)
or
SELECT * from db2.sysobjects where not in (SELECT * from db1.sysobjects)
This works if you want to analyse the difference in term of tables
and fields.
To have a deeper analysis (fields of different types) it is more difficult
but possible with other system tables like syscolumns.
Gilles
"Gary" <gb@.nospam.com> wrote in message news:<ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl>...

quote:

---

[col
or=darkred]
> is there any utility or program available that will compare two versions o
f
> the same SQL Server database in terms of definition and objects. I don't
> want to compare data. I need to see the differences in tables columns vie
ws
> etc.
> Thanks,
> Gary[/color]

---

sqlsql

Compare 2000 with 2005 Performance - 32/64 Decide to buy?

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

Try:

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLServer2005_WhyUpgrade.doc

|||I'm looking specifically for the answer to," Why move to 64 bit from 32 bit"

The document is good, but only addresses moving from 2000 to 2005 and not justification of moving hardware to 64 bit.

Ian

|||The main reason to go 64bit is better memory performance for large amounts of memory (ie 16gb+), and better CPU performance for 64bit applications. The other reason is, everything will be 64bit at some time. It is the future.

Now the problems. The main problem you will have with 64bit is with ODBC drivers and other 3rd party drivers not supporting 64bit.

Before you even entertain the idea of changing, check to make sure everything you run on the server is compatable and SUPPORTED in Windows 2003 64bit.

|||

FYI

I have a few 2005 (SS2k5 x64) installs and many 2000 (SS2K x32) installs.

Last evening a developer emailed and said her select was taking to long to run, 22 minutes on the SS2K x32. I cut\pasted her select and executed it on a new X64 Sql Server 2005 SP2 (3050). The databases are identical as the X64 will be her new Sql server.

The sql ran in 36 seconds.

I look good

|||

See if this helps:

http://www.microsoft.com/sql/editions/64bit/default.mspx

Compare 2000 with 2005 Performance - 32/64 Decide to buy?

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

Try:

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLServer2005_WhyUpgrade.doc

|||I'm looking specifically for the answer to," Why move to 64 bit from 32 bit"

The document is good, but only addresses moving from 2000 to 2005 and not justification of moving hardware to 64 bit.

Ian

|||The main reason to go 64bit is better memory performance for large amounts of memory (ie 16gb+), and better CPU performance for 64bit applications. The other reason is, everything will be 64bit at some time. It is the future.

Now the problems. The main problem you will have with 64bit is with ODBC drivers and other 3rd party drivers not supporting 64bit.

Before you even entertain the idea of changing, check to make sure everything you run on the server is compatable and SUPPORTED in Windows 2003 64bit.

|||

FYI

I have a few 2005 (SS2k5 x64) installs and many 2000 (SS2K x32) installs.

Last evening a developer emailed and said her select was taking to long to run, 22 minutes on the SS2K x32. I cut\pasted her select and executed it on a new X64 Sql Server 2005 SP2 (3050). The databases are identical as the X64 will be her new Sql server.

The sql ran in 36 seconds.

I look good

|||

See if this helps:

http://www.microsoft.com/sql/editions/64bit/default.mspx

Compact and Repair in an adp

Hi,

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.

I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

Thanks!

J~JA wrote:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

No idea. You can definitely use Query Analyzer to run a
DBCC SHRINKDATABASE command.

If you expect the database to grow back to its previous size and
stay there, then you shouldn't shrink it. If you expect it to
stay at its reduced size, then you should.|||JA (jarmour@.kc.rr.com) writes:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do
that from Access? Will it not mess anything up?

To me that sounds like tools for the local Access database.

In any case, one million rows is not that much, unless the rows are
extremely wide.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Common error codes for ADO & DMO

hi,
The error codes I am getting for errors like SQL server not available,
invalid sql credentials, login not a database user etc. are not same for ADO
& DMO. Is there a way to get common error codes for ADO & DMO?
Thanks.
- AjeyFor DMO I am using the IDispatch pointer and converting the EXCEPINFO->wCode
to HRESULT.
"Ajey" <ajey5@.hotmail.com> wrote in message
news:eLRrRVOCFHA.3820@.TK2MSFTNGP11.phx.gbl...
> hi,
> The error codes I am getting for errors like SQL server not available,
> invalid sql credentials, login not a database user etc. are not same for
ADO
> & DMO. Is there a way to get common error codes for ADO & DMO?
> Thanks.
> - Ajey
>