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