Re: Altering columns...getting complicated...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In message <OU#WxZ8WFHA.2420@xxxxxxxxxxxxxxxxxxxx>, YYZ <none@xxxxxxxx> 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@xxxxxxxxxxxxxxxxx

ZAD Software Systems                Web   :  www.zadsoft.com
.

Relevant Pages

  • Re: Sync/Merge local sql compact databases to single global database
    ... with the host database you have the advantage of a unique number and knowing ... ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ... CONSTRAINT fkPlayerID FOREIGN KEY REFERENCES Players ... sync capabilities of compact SQL handle the sessions table? ...
    (microsoft.public.sqlserver.ce)
  • replacing sp_MSinsTable1
    ... CREATE TABLE [Table1] ( ... CONSTRAINT PRIMARY KEY CLUSTERED ... The consolidated database looks like: ...
    (microsoft.public.sqlserver.replication)
  • RE: Problems saving data to SQL database, timeout expired
    ... Thank you for having a look at the database structure. ... -- Installing the "event" database. ... ADD CONSTRAINT PK_AlarmState ... PRIMARY KEY ...
    (microsoft.public.sqlserver.server)
  • Sync/Merge local sql compact databases to single global database
    ... for one of the tables i have had to use auto incrementing integer which works fine for the local database but i would like to merge all of the users databases into a global database. ... ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ... CONSTRAINT fkPlayerID FOREIGN KEY REFERENCES Players ... how will the sync capabilities of compact SQL handle the sessions table? ...
    (microsoft.public.sqlserver.ce)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)