ALTER TABLE problem!

Question

Post reply

ALTER TABLE problem!

Larsi1971

Ten Centuries

Points: 1081
More actions
January 30, 2004 at 6:04 am

#107517

Hello!
I'm having problems up here in Norway. Trying to get alter table and alter column to work. Is it so that the script has to look like this?
ALTER TABLE dbo . BLOBS
ALTER COLUMN BLOBS_ID   int NOT NULL
ALTER TABLE dbo . BLOBS
ALTER COLUMN TRANSACTION_ID   int NOT NULL
ALTER TABLE dbo .BLOBS                                                                      ALTER COLUMN PRIMARY_KEY   varchar (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
I heared that I could script one database, and replace all create with Alter. Is this true? I should remove [] also - I know that, but if I should have alter table "tablename" before each column - it would be a large job to do that
I was thinking of using this to maintain several databases, but I'm beginning to beleive that this doesn't work very vell. Could anyone help me how to maintain several databases?

-Lars

Please only reply to this newsgroup. All mails would be bounced back.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

What are you trying to do?

Do you have a table and are just trying to change the columns in the table?

-SQLBill

Larsi1971 Ten Centuries Points: 1081 More actions · Answer 2

I'm having several tables - which I would maintain at the sime time.

Was actually thinking of a job that I could run to maintain the databases - but I can't run a job before I know how to maintain the database first

-Lars

Please only reply to this newsgroup. All mails would be bounced back.

Larsi1971 Ten Centuries Points: 1081 More actions · Answer 3

I'm trying to change the column yes!

-Lars

Please only reply to this newsgroup. All mails would be bounced back.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

You can alter several columns at once:

ALTER TABLE mytablename ALTER COLUMN mycolumnname1 INT,

mycolumnname2 VARCHAR(2),

etc....

Check out ALTER TABLE in the BOL. Use the Index tab and enter ALTER TABLE.

-SQLBill

BOL=Books OnLine=Microsoft's SQL Server Help, installed as part of the Client Tools, can be found at Start>Programs>Microsoft SQL Server>Books OnLine

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

Jonathan

SSC-Insane

Points: 20427

January 30, 2004 at 10:40 am

#492376

Did you test this?

--Jonathan

Jonathan SSC-Insane Points: 20427 More actions · Answer 6

Lars--

If you make changes to your design using ALTER TABLE, just save the scripts so you can apply them to all relevant databases.

--Jonathan

SQLBill SSC Guru Points: 51440 More actions · Answer 7

No I didn't.

I checked the BOL and while there wasn't an exact example, it did infer that you could do several ALTER COLUMN that way. It did give a similar example for multiple ADD COLUMN within one ALTER TABLE command.

That was also why I directed the poster to check the BOL.

-SQLBill

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

Well, I knew you hadn't.

Here's the syntax from BOL:

ALTER TABLE table
{ [ ALTER COLUMN column_name
    { new_data_type [ ( precision [ , scale ] ) ]
        [ COLLATE < collation_name > ]
        [ NULL | NOT NULL ]
        | {ADD | DROP } ROWGUIDCOL }
    ]
}

No support for multiple columns there...

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Saving the scripts and applying it to your databases like Jonathan suggested, sounds pretty obvious to me.

If you have several databases with the same structure, I would create my own rollout and maintainance routines. In the end you'll need them away.

Another question:

PRIMARY_KEY varchar (512) ?

What are you storing there?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 10

Jonathan,

Thanks for pointing out the error of my ways. I went back and relooked at the BOL, and the paragraph on ALTER COLUMN even states "the given column" and on ADD COLUMN says "one or more column...". Ahhh, I should have read deeper the first time.

Thanks for correcting me.

-SQLBill