How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?