I am working in SQL Server 2005, SQL Management Studio.  I have made a number
of changes over the last week in my test database, and now need to migrate
those changes to a production DB.  How do I get a list of what tables, views,
sprocs, etc haved been changed?

Schema Change History is useless because it is tied to the default trace. 
Because this is a development copy of SQL Server on my desktop and my machine
is up and down at least once every day, the trace gets reset each time.

The list of tables, view, etc. gives me the name of the object, the schema
it is part of and the creation date, which I could care less about.  I can
find no option to show the Last Modified Date.  Someone told me they do not
think that information is recorded, and I find that hard to believe!

Try Red Gate's SQL Compare.  www.red-gate.com ... not only does it identify
all of the differences, it builds you a change script and will even run it
for you.

If you just want last modified date and would rather build your own scripts
(why, I'm not sure), this information is stored in sys.procedures,
sys.views, sys.tables, etc.  Have you looked at these catalog views?




Show quoteHide quote

Thanks for the suggestion...
<(why, I'm not sure)>  because my boss will probably not approve of $395.

But I see I can download a trial version so maybe I can prove its value.


Show quoteHide quote

Hi SilkCityFlorida,

<(why, I'm not sure)>  because my boss will probably not approve of
$395.

Well, may be your boss will approve $99 for our SQL Server Comparison
Tool (SCT)?

AlfaAlfa's SCT - www.sql-server-tool.com - can check the schema
differences between two databases, as well as it can compare data
between databases.

Comparison "sessions" can be saved and re-played later without need of
re-entering the parameters. Command line parameter can be used to
fully automate comparisons.

SCT works with SQL Server 2005, 2000 and 7.0 and between these
versions.  SCT has 30 days of free evaluation period, which can be
extended to 90 days.


Dariusz Dziewialtowski.

On Nov 1, 12:45 pm, darek <alfaa***@gmail.com> wrote:
Show quoteHide quoteHello SilkCityFlorida,

you don't need to pay $395, not even $99 - you can get xSQL Software's
xSQL Object (www.xsqlsoftware.com) for free. Here are the details:
- first off you download and install it, no registration or
activation is required - fully functional you just use it and get the
job done;
- after the first two weeks there are two checks we do: if the
databases you are using it with are SQL Server Express then nothing
changes, you simply continue to use and enjoy it for free; if the
databases you are using are not SQL Server Express but rather some
other edition of SQL Server then, if you have less then a certain
number of objects (number of tables, views, stored procedures etc) in
your database again, nothing changes you continue to use it for free
with no strings attached. Only, if your database is not SQL Server
Express and you have more than the given number of objects then we
think you can certainly afford it and kindly ask you to purchase the
product so that we can continue developing :)

And, here are a couple more bonus points:
  - the scripting functionality which by the way allows you to script
both schema and data together, is completely free regardless of SQL
Server edition or number of objects in the database (and which I
believe is sold as a separate product by other software publishers)
- lastly, we are located just a shout away from you - in the
beautiful city of Atlanta.

Thanks,
JC

The information is recorded in 2005 (not earlier versions). You find the information in the catalog
views. For instance:

SELECT name, modify_date FROM sys.tables

Show quoteHide quote

SilkCityFlorida (SilkCityFlor***@discussions.microsoft.com) writes:
Show quoteHide quoteWhile Red Gate's SQL Compare can save you after the fact, the true answer
is that all objects should be kept under version control, and you check
out the objects as you need to change them. Once you are done, you check
in the changes. Then you can extract the changed files by seeing what
has changed since the previous baseline.

In essence this is no different than if you had worked with a C# or
VB program.

That works for stored procedures but it can be harder to do for tables, e.g.
if you add indexes, change column datatypes, etc.  What do you store?  A new
CREATE TABLE script?  What good does that do you?

Red-Gate allows you to compare your test and production systems, build a
script that will synchronize them, and then store THAT in source control.

All depends on what you're after, I guess.  Personally I don't consider SQL
Compare an "after the fact" tool.

Aaron

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
The CREATE TABLE script is of course essential if you need to build a
new database. Which we do a lot for various reasons. The most obvious
reason is that we have a new customer...

But of course, you need to version-control your change scripts as well.

And I'm just saying that this can be done with a tool like red-gate (compare
your test system to any empty database).  Personally I don't find much value
in massaging an old CREATE TABLE statement to fit the way the table looks
today... but hey, everyone has different preferences.

A

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
Of course, if you think that you will never build that database from
scracth again, the CREATE TABLE thing is not that hot.

I, for my part, *know* that I will build that database from scratch again.

On Oct 29, 4:23 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
Show quoteHide quoteHi Aaron,

SQL Compare 6 Pro (released in June) is now not only able to compare a
live database, but also to compare a database's schema creation
scripts with a target database to generate a SQL change script. So in
theory it is possible to maintain the table creation scripts and
nothing more. Of course there are some situations where this doesn't
work, for example if a significant change has been made to the target
database, such as a table split or an object rename. However, this is
no different to problems encountered comparing two live databases
where one has been significantly changed or refactored.

Kind regards,

David Atkinson
Red Gate Software

SQLDMO with VB6
SQL Profiler won't allow a developer to login
SQL2k Profiler: load big trace file into table, on small system ?
BCP with a query in a file, or SQLCMD outputting tab-delimited?
SQL Server 2000 EM vs. SQL Server 2005 MS