alter table defaults - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

alter table defaults

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2004-11-26 : 07:14:31

Is it possible to alter the defaults for columns in an existing table without creating new fields, dropping the old field then renaming the new one?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 08:23:07

create table b (id int)
alter table b alter column id varchar(10)

Go with the flow & have fun! Else fight the flow

iminore
Posting Yak Master

141 Posts

Posted - 2004-11-26 : 08:41:35

I want to alter the default for a column.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 09:07:27

yeah sorry...


if object_id('b') is not null
	drop table b

--use northwind
create table b (id int, id1 int constraint con_1 default 5)
insert into b(id)
select 11 union all
select 12 union all
select 13 union all
select 14

select * from b

ALTER TABLE b
	DROP CONSTRAINT con_1

go
ALTER TABLE b ADD CONSTRAINT
	con_1 DEFAULT 10 FOR id1

go

insert into b(id)
select 1 

select * from b

Go with the flow & have fun! Else fight the flow

iminore
Posting Yak Master

141 Posts

Posted - 2004-11-26 : 10:02:10

Yes, that works - many thanks. The lesson seems to be that column defaults are stored as constraints. Sadly the design/properties option doesn't show these constraints so how can we get the name(s) of existing defaults?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 10:14:51

sp_helpconstraint 'TableName'

will get you the info you need

Go with the flow & have fun! Else fight the flow

iminore
Posting Yak Master

141 Posts

Posted - 2004-11-26 : 10:25:47

Yes again. For table 'T' column 'C' the constraint seems to be named DF_T_DefaultC.

Take a bow!

iminore
Posting Yak Master

141 Posts

Posted - 2004-11-26 : 11:58:13

sorry, got that wrong - DF_T_C