I have been looking to alter a column in a table and add a default value. Im trying to find a good script example to do this. my question now is why cant I do it in the GUI tool? When I select the table select alter and then select the column and press alter everything is grayed out. Im logging in as the admin in the sysadmin group. any thoughts as to why the qui will not let me alter the table?
Jim

You can add a column, but you cannot alter a column (except to change the length of a varchar column). This may change in version 8.2 (Stinger) due out later this year.

OK I accept that. Can you help me with an alter table script. I cant seem to find a good example to base mine off of.
Jim

You HAVE to drop and recreate them.

Newbie

You have to be kidding me. I cant accept that I cant add a default after the table is created. I mean for gods sake i have the table populated.
Jim

God has nothing to do with it, and YOU WILL ACCEPT IT.

However, it is quite easy to do the following (assume your table is called table_name)

create new table called table_name2 the way you want all the columns defined.

insert into table_name2 select * from table_name
(if the table is very large and you don't want to log the inserts, then use not logged initially and execute the insert in the same unit of work, and then commit)

rename table table_name to table_name3

rename table table_name2 to table_name

create indexes, views, rebind, etc

I am still in complete shock that I have to do all off this just to add a default. Ill remember to have this worked out before i creat the next batch of tables.
Jim

You need to think carefully about any data integrity problems that might occur with changes to the existing values of the table if you switch to new default value. I think this may be why changing it on the fly is not allowed.

Unfortunatly the issue was not discused before the table was created and now there is a need for a default value. I didnt change an exhisting one, i needed to create one.
Thanks for all the help
Jim