Alter Table Question

I'm setting up a DTS to modify a table, and I need to "reset" the identity
field. I had originally wanted to do this with an ALTER TABLE command, but
apparently it isn't as straightforward as that. So at this point I believe
I need to drop the table and recreate it. Is this the case? Assuming it
is, I've run into the following problem. I need to add a description to the
fields when I re-create the table, but I can't seem to find the syntax. For
my CREATE function, it's as simple as:
CREATE TABLE TstTable
(
ACC_ID int identity primary key,
Code varchar(50),
CodeType varchar(15),
ActionType varchar(10),
ActionBy varchar(50),
ChangeDate datetime,
ChangeTime varchar(20)
)
...but I can't figure out how to put a description for each field in.
Anyone know the syntax, or if it's impossible?
Thanks,
James
Nevermind...seems like sp_addextendedproperty will do what I'm looking for.
Thanks anyway!
"James" <cppjames@.aol.com> wrote in message
news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> I'm setting up a DTS to modify a table, and I need to "reset" the identity
> field. I had originally wanted to do this with an ALTER TABLE command,
but
> apparently it isn't as straightforward as that. So at this point I
believe
> I need to drop the table and recreate it. Is this the case? Assuming it
> is, I've run into the following problem. I need to add a description to
the
> fields when I re-create the table, but I can't seem to find the syntax.
For
> my CREATE function, it's as simple as:
> CREATE TABLE TstTable
> (
> ACC_ID int identity primary key,
> Code varchar(50),
> CodeType varchar(15),
> ActionType varchar(10),
> ActionBy varchar(50),
> ChangeDate datetime,
> ChangeTime varchar(20)
> )
> ...but I can't figure out how to put a description for each field in.
> Anyone know the syntax, or if it's impossible?
> Thanks,
> James
>
|||James -
Have a look at dbcc checkident for the other issue of resetting the
identity.
Mike John
"James" <cppjames@.aol.com> wrote in message
news:OE4FGA72EHA.1192@.tk2msftngp13.phx.gbl...
> Nevermind...seems like sp_addextendedproperty will do what I'm looking
> for.
> Thanks anyway!
>
> "James" <cppjames@.aol.com> wrote in message
> news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> but
> believe
> the
> For
>

Alter Table Question

I'm setting up a DTS to modify a table, and I need to "reset" the identity
field. I had originally wanted to do this with an ALTER TABLE command, but
apparently it isn't as straightforward as that. So at this point I believe
I need to drop the table and recreate it. Is this the case? Assuming it
is, I've run into the following problem. I need to add a description to the
fields when I re-create the table, but I can't seem to find the syntax. For
my CREATE function, it's as simple as:
CREATE TABLE TstTable
(
ACC_ID int identity primary key,
Code varchar(50),
CodeType varchar(15),
ActionType varchar(10),
ActionBy varchar(50),
ChangeDate datetime,
ChangeTime varchar(20)
)
...but I can't figure out how to put a description for each field in.
Anyone know the syntax, or if it's impossible?
Thanks,
JamesNevermind...seems like sp_addextendedproperty will do what I'm looking for.
Thanks anyway!
"James" <cppjames@.aol.com> wrote in message
news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> I'm setting up a DTS to modify a table, and I need to "reset" the identity
> field. I had originally wanted to do this with an ALTER TABLE command,
but
> apparently it isn't as straightforward as that. So at this point I
believe
> I need to drop the table and recreate it. Is this the case? Assuming it
> is, I've run into the following problem. I need to add a description to
the
> fields when I re-create the table, but I can't seem to find the syntax.
For
> my CREATE function, it's as simple as:
> CREATE TABLE TstTable
> (
> ACC_ID int identity primary key,
> Code varchar(50),
> CodeType varchar(15),
> ActionType varchar(10),
> ActionBy varchar(50),
> ChangeDate datetime,
> ChangeTime varchar(20)
> )
> ...but I can't figure out how to put a description for each field in.
> Anyone know the syntax, or if it's impossible?
> Thanks,
> James
>|||James -
Have a look at dbcc checkident for the other issue of resetting the
identity.
Mike John
"James" <cppjames@.aol.com> wrote in message
news:OE4FGA72EHA.1192@.tk2msftngp13.phx.gbl...
> Nevermind...seems like sp_addextendedproperty will do what I'm looking
> for.
> Thanks anyway!
>
> "James" <cppjames@.aol.com> wrote in message
> news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> I'm setting up a DTS to modify a table, and I need to "reset" the
>> identity
>> field. I had originally wanted to do this with an ALTER TABLE command,
> but
>> apparently it isn't as straightforward as that. So at this point I
> believe
>> I need to drop the table and recreate it. Is this the case? Assuming it
>> is, I've run into the following problem. I need to add a description to
> the
>> fields when I re-create the table, but I can't seem to find the syntax.
> For
>> my CREATE function, it's as simple as:
>> CREATE TABLE TstTable
>> (
>> ACC_ID int identity primary key,
>> Code varchar(50),
>> CodeType varchar(15),
>> ActionType varchar(10),
>> ActionBy varchar(50),
>> ChangeDate datetime,
>> ChangeTime varchar(20)
>> )
>> ...but I can't figure out how to put a description for each field in.
>> Anyone know the syntax, or if it's impossible?
>> Thanks,
>> James
>>
>

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers