change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Change Column Ordinal Position with T-SQL

Hi Friends ...
I need to change the ordinal position of a column in an existing table using
T-SQL through QA - anybody know the proprer syntax?
Thanks for your help ...Hi,
If you don't use bad syntax as "select * from MyTable", you shouldn't have
tou deal with ordinal position.
Anyway, there's no solution to change ordinal position, because there
shouldn't be any need to.
The only way to do this is to drop - recreate the table.
JN.
"bill_morgan" wrote:

> Hi Friends ...
> I need to change the ordinal position of a column in an existing table usi
ng
> T-SQL through QA - anybody know the proprer syntax?
> Thanks for your help ...|||If you want to see the script involved with your table,
do this in Enterprise Manager but before saving the changes,
click on the icon to see the script.
"Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote in
message news:09BBB5B8-2830-474F-BC84-471F33DAFCA7@.microsoft.com...
> Hi,
> If you don't use bad syntax as "select * from MyTable", you shouldn't have
> tou deal with ordinal position.
> Anyway, there's no solution to change ordinal position, because there
> shouldn't be any need to.
> The only way to do this is to drop - recreate the table.
> JN.
> "bill_morgan" wrote:
>|||> I need to change the ordinal position of a column in an existing table
> using
> T-SQL through QA - anybody know the proprer syntax?
There is no such thing, at least not in a single statement.
http://www.aspfaq.com/2528|||Actually, the local admin changed orginal position, and I wanted to make sur
e
there wasn't syntax in this regard I hadn't learned yet. He must have done i
t
through EM.
Thanks ...
"Jean-Nicolas BERGER" wrote:
> Hi,
> If you don't use bad syntax as "select * from MyTable", you shouldn't have
> tou deal with ordinal position.
> Anyway, there's no solution to change ordinal position, because there
> shouldn't be any need to.
> The only way to do this is to drop - recreate the table.
> JN.
> "bill_morgan" wrote:
>|||Thanks for your response ... mainly wanted to see if it's possible in QA
rather than having to use EM. Now I know ...
Thanks again ...
"Raymond D'Anjou" wrote:

> If you want to see the script involved with your table,
> do this in Enterprise Manager but before saving the changes,
> click on the icon to see the script.
> "Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote
in
> message news:09BBB5B8-2830-474F-BC84-471F33DAFCA7@.microsoft.com...
>
>|||Don't forget, this is easily done on an empty table.
On a table with millions of rows, you can imagine the time this operation
can take.
But as others have said, column order should not be a concern.
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:77D61C48-5CF5-4502-B35C-11D8ACA160B6@.microsoft.com...
> Actually, the local admin changed orginal position, and I wanted to make
> sure
> there wasn't syntax in this regard I hadn't learned yet. He must have done
> it
> through EM.
> Thanks ...
> "Jean-Nicolas BERGER" wrote:
>