SA Developer .NET

I need to write n script to alter a column in a table that is already in use .

The column looks like this: ColumnName (DateTime, not null)

But I need to add n default value of '1900\01\01' and update all the rows in the column that do not have a value in it already for this column.

Please help!

The T-SQL to add the default looks like this

ALTER TABLE <tablename>

ADD CONSTRAINT <constraintname>

DEFAULT '1900/01/01' for <columnname>

Since the column is already not null there should not be any rows without a value in it so there should be no reason to run an update afterwards.

Hi there,

I think this should solve your problem quite nicely:

DECLARE @DoesExist INT

SET @DoesExist = 0

@DoesExist = ISNULL(SC.id, 0)

sysobjects so

LEFT JOIN

syscolumns SC

So.id = sc.id

sc.name like 'columnname'

IF (@DoesExist <= 0)

ALTER TABLE table_name ADD columnname DATETIME DEFAULT '1900/01/01' NOT NULL

GO

This will then add the new column, and create it with a default date as you require. The code before it will check that the column does not already exist in the table, so as to avoid errors. DBA's can be picky about these things

Hope it helps,

Regards,

The H.............................

WhoKnowsWhat:

The column looks like this: ColumnName (DateTime, not null)

But I need to add n default value of '1900\01\01' and update all the rows in the column that do not have a value in it already for this column.

How can there be rows that do not already have a value when the column is set to be "not null"?

Heat_Rash:

How can there be rows that do not already have a value when the column is set to be "not null"?

I think the OP wants it to be not null, but can't make it so because there are existing rows with null.

Hey all,

Sorry about the whole not null thing. I didn;t think properly - duh!

Thanx for the responses

Keep it up!