generation of sql for an alter column etc

[Jeff Kish]

Hi.

I have a database I need to supply something (I'm assuming a t-sql script..
maybe something else is better) to update customer tables with.
The operations include mostly changing varchar lengths, though a couple of
columns were renamed.

I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
to generate the scripts.

I can't just send alter table scripts because I'm involving all sorts of
constraints that have to be disabled/or dropped, the alter made, then have
them enabled/ or re-created.

Basically I'm hoping to get the tools to do the rather large amount of work
for me. I'm targetting sql server 2000.

Can someone make a knowledgeable suggestion?

Regards
Jeff Kish

[Robert Klemme]

Re: generation of sql for an alter column etc

Jeff Kish wrote:[color=blue]
> Hi.
>
> I have a database I need to supply something (I'm assuming a t-sql script..
> maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple of
> columns were renamed.[/color]

Not a good idea IMHO although you can use sp_rename.
[color=blue]
> I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
> to generate the scripts.
>
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.[/color]

Then generate the SQL for the target state and insert drops yourself.
[color=blue]
> Basically I'm hoping to get the tools to do the rather large amount of work
> for me. I'm targetting sql server 2000.
>
> Can someone make a knowledgeable suggestion?[/color]

I don't think you will be able to get this out of EM - at least not
directly. It would basically mean to trace your operations and generate
SQL from that. I don't think EM will do that for such a complex set of
operations. You'll have to do some manual work.

Kind regards

robert

[Erland Sommarskog]

Re: generation of sql for an alter column etc

Jeff Kish (jeff.kish@mro. com) writes:[color=blue]
> I have a database I need to supply something (I'm assuming a t-sql
> script.. maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple
> of columns were renamed.
>
> I'd like to maybe figure out how to get Enterprise Manager or Query
> Analyzer to generate the scripts.
>
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
>
> Basically I'm hoping to get the tools to do the rather large amount of
> work for me. I'm targetting sql server 2000.[/color]

Composing change scripts for production environments is not a task to
take lightly. Particularly not if you have to apply them while the system
is operating. (If the system closes for business, you may be able to repair
a disaster by restorin a backup.)

It requires good understanding of what can go wrong, and how to prevent
that. For instance, if you need to drop constraints to alter a column,
you should probably wrap that in a transaction, so you don't end up with
losing the constraint.

At the same time, ALTER operations that require changes to the physical
data pages, can take a huge toll on the transaction log, causing it to
grow rapidly. (Changing varchar lengths should be metadata so that should
be safe.)

You can use Enterprise Manager to have it to generate change scripts.
However, there are several flaw in those scripts, and you need to review
them carefully, and also make several changes to them. For instance, the
transaction scope in those scripts are wacko.

What may prove to be a show-stopper is that EM works with SQL 6.5 as its
target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
update as create new table and copy data over. Which sometimes is the right
thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.

There are other tools on the market. Red Gate's SQL Compare get a lot
of positive acclaim, but I have not used it myself.

One potential problem is that you don't know the name of the constraints,
because they were added without a name, so all there is a system-generated
name. In this case, you need to retrieve the name, and then run DROP
CONSTRAINT dynamically. I would suggest that you restore the constraints
with a given name.

Speaking of constraints, make sure that you re-add them WITH CHECK. The
script from EM uses WITH NOCHEK, which means that they are not checked. This
is a lot faster, but it also means that the optimizer will neglect them,
which can have expensive consequences.

Finally, before you run in production, test on a copy of production!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jeff Kish]

Re: generation of sql for an alter column etc

On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.se> wrote:
[color=blue]
>Jeff Kish (jeff.kish@mro. com) writes:[color=green]
>> I have a database I need to supply something (I'm assuming a t-sql[/color][/color]
<snip>[color=blue]
>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.[/color]
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.[color=blue]
>
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
>[/color]
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.
[color=blue]
>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
>[/color]
target is only sql server 2000 right now.[color=blue]
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
>[/color]
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?
[color=blue]
>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>
>Finally, before you run in production, test on a copy of production![/color]
of course! the scars I have should remind me of that. :> )

thanks so much.
Jeff Kish

[Erland Sommarskog]

Re: generation of sql for an alter column etc

Jeff Kish (jeff.kish@mro. com) writes:[color=blue][color=green]
>>You can use Enterprise Manager to have it to generate change scripts.
>>However, there are several flaw in those scripts, and you need to review
>>them carefully, and also make several changes to them. For instance, the
>>transaction scope in those scripts are wacko.
>>[/color]
> Can you tell me how? I'm having some problem seeing how to get the
> equivalent alter table etc scripts out of EM. I looked, honest. I'll
> even look some more.[/color]

Right-click table and select Modify Table. As I said, it is not likely
it will generate ALTER TABLE commands, those you will have to write
yourself. But at least you will get some code to recreate constraints.
Just be sure to change WITH NOCHECK to WITH CHECK.
[color=blue][color=green]
>>One potential problem is that you don't know the name of the constraints,
>>because they were added without a name, so all there is a system-generated
>>name. In this case, you need to retrieve the name, and then run DROP
>>CONSTRAINT dynamically. I would suggest that you restore the constraints
>>with a given name.
>>[/color]
> mmm not sure I understand.. they are originally added specifically. can
> I just disable them or do I need to drop them?[/color]

The error message from ALTER TABLE makes it clear that you have to
drop the constraint. Keep in mind that a FK column must match the
PK column it refers to, so if you change a PK/FK pair, you need to
drop constraints for both columns before changing.



Yes, it follows that from what I say that you should mainly generate
the script manually. This may seem like a crazy idea, but since it's
so important that you understand what the script does, it can be
dangerous to leave that in the hands of a tool.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx