How to erase hundreds of DEFAULT values?

SQL Apprentice Question
I'm newbie (yet) in MS SQL2k and I have a big problem, so I would like
to please for help:


There are 9 big databases with a _lot_ of user tables.


I had to insert 2 new fields into _every_ user tables (at the end). It
succeeded, but noticed that I made a mistake: set a Default value for
them. But they should had been empty :-/


These are the new fileds:


Modify_vC varchar (50) NULL Default: suser_sname()
Modify_Dt datetime NULL Default: getdate()


So I wrote (mainly copy-pasted from this newsgroup Thanks for it! :) a
script, but it does not change DEFAULT's value.


(
I tried also to attach "DEFAULT NULL" at the end of this line, but it
throws an error.
exec ('ALTER TABLE ' + @table_name + ' ALTER COLUMN Modosito_vC
varchar (50) NULL DEFAULT NULL')
)


How can I erase DEFAULT's value for these two fields? By hand, it would
take for a year...
(to run 9x (9 DB's) is OK, but to table to table would be horrible)


Thanks for your help.
Bálint


The script:
------------------------- script --------------------------
USE database1


declare @table_name sysname
declare tables_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)


from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0


open tables_cursor


while 1 = 1
begin
fetch next from tables_cursor into @table_name


if @@error != 0 or @@fetch_status != 0 break


exec ('ALTER TABLE ' + @table_name + ' ALTER COLUMN Modosito_vC
varchar (50) NULL')
exec ('ALTER TABLE ' + @table_name + ' ALTER COLUMN Modositas_Dt
datetime NULL')


end


close tables_cursor
deallocate tables_cursor


go


Celko Answers
>> had to insert 2 new fields [sic] into _every_ user tables (at the end). It succeeded, but noticed that I made a mistake: set a Default value for them. But they should had been empty :-/ <<


No, they should not have been added to the schema at all. First of
all, they are not attributes in a proper data model. They have nothing
to do wiht the entities to which they are attached.

Secondly, their names include their data type in violation of ISO-11179
conventions and good programming. Thaty is a pure newbie thing where
you carry over old programming habits to the new language. Also, not
knowing the columns and fields are totally different concepts.


Third, it is illegal under SOX and several other laws have audit
information in the same schema as the data. The audit trail has to be
external to the data and requires at least two independent
confirmations. Any single user with full rights on your tables can
change or destroy the audit trail.