ALTER TABLE problem!
-
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. -
What are you trying to do?
Do you have a table and are just trying to change the columns in the table?
-SQLBill
-
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. -
I'm trying to change the column yes!
-Lars
Please only reply to this newsgroup. All mails would be bounced back. -
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
-
Did you test this?
--Jonathan -
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 -
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
-
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 -
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] -
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply