Altering columns...getting complicated...

I have a need to write many scripts to alter a LOT the underlying database
structure of our database in the field. We have many fields out there with
a type of float, and I've been told to change those to numeric(19,5) -- easy
enough. Unless there is a constraint, in which case I have to drop the
constraint, alter the field, add the constraint back in. Easy enough again,
once you know what you are doing.
Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
easy enough again, unless they have a default -- use the same scheme as
above, and it all works. UNLESS they are part of a primary key. Uh oh --
now I hit something I don't know how to solve...
What I'm thinking is that I should dump all of the indexes and primary keys
and defaults out of all tables, and then just rebuild them all from scratch.
However, this database was "created" by using the Access upsizing wizard, so
I don't know all the primary key names, constraint names, etc.
Can anyone point me in the right direction to dump all indexes and defaults
on every column in a database? I can re-create them pretty easily...
Any advice would be appreciated, or even an alternate method to do what I
need to do.
Thanks in advance.
Matt
In message <OU#WxZ8WFHA.2420@.TK2MSFTNGP12.phx.gbl>, YYZ <none@.none.com>
writes
>I have a need to write many scripts to alter a LOT the underlying database
>structure of our database in the field. We have many fields out there with
>a type of float, and I've been told to change those to numeric(19,5) -- easy
>enough. Unless there is a constraint, in which case I have to drop the
>constraint, alter the field, add the constraint back in. Easy enough again,
>once you know what you are doing.
>Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
>easy enough again, unless they have a default -- use the same scheme as
>above, and it all works. UNLESS they are part of a primary key. Uh oh --
>now I hit something I don't know how to solve...
>What I'm thinking is that I should dump all of the indexes and primary keys
>and defaults out of all tables, and then just rebuild them all from scratch.
>However, this database was "created" by using the Access upsizing wizard, so
>I don't know all the primary key names, constraint names, etc.
>Can anyone point me in the right direction to dump all indexes and defaults
>on every column in a database? I can re-create them pretty easily...
>Any advice would be appreciated, or even an alternate method to do what I
>need to do.
>
Use a CURSOR to enumerate the SYSINDEXES system table in your database
to find all the indexes on it. Alternatively, if you tied this up with
the INFORMATION_SCHEMA.TABLES you can list the indexes on a table by
table basis.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Matt,
YYZ wrote:
> I have a need to write many scripts to alter a LOT the underlying
> database structure of our database in the field. We have many fields
> out there with a type of float, and I've been told to change those to
> numeric(19,5) -- easy enough. Unless there is a constraint, in which
> case I have to drop the constraint, alter the field, add the
> constraint back in. Easy enough again, once you know what you are
> doing.
> Now, they tell me to change all the nvarchar(XX) fields to
> varchar(XX) -- easy enough again, unless they have a default -- use
> the same scheme as above, and it all works. UNLESS they are part of
> a primary key. Uh oh -- now I hit something I don't know how to
> solve...
> What I'm thinking is that I should dump all of the indexes and
> primary keys and defaults out of all tables, and then just rebuild
> them all from scratch. However, this database was "created" by using
> the Access upsizing wizard, so I don't know all the primary key
> names, constraint names, etc.
> Can anyone point me in the right direction to dump all indexes and
> defaults on every column in a database? I can re-create them pretty
> easily...
> Any advice would be appreciated, or even an alternate method to do
> what I need to do.
you can perhaps search www.sqlservercentral.com... there's plenty of
maintenance scripts...
ie: http://www.sqlservercentral.com/scri...utions/935.asp to drop
and recreate all indexes on a db..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Alter Table fails because column is being replicated

This is a follow up to my post 7/30/2007
<please review>
I have been unsuccessful in removing this column
do I have to rebuild from scripts then migrate data?
Thanks
Mark J. Soule
Can you confirm that you have removed the constraint, run
sp_removedbreplication (assuming the database isn't used as a publisher or
subscriber) and run sp_MSunmarkreplinfo and still you can't change the table?
Doing all of these should be enough.
Cheers,
Paul Ibison
|||Hi Paul;
Yes I removed all identity range constraints and ran MSunmarkreplinfo.
I gave up and re-created the entire database from scripts then imported
data to the new database.
It was a pain, but it is complete.
thanks
MJ
"Paul Ibison" wrote:

> Can you confirm that you have removed the constraint, run
> sp_removedbreplication (assuming the database isn't used as a publisher or
> subscriber) and run sp_MSunmarkreplinfo and still you can't change the table?
> Doing all of these should be enough.
> Cheers,
> Paul Ibison