Hello!

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
----------------------------- end -----------------------

That's a bit 'oops'.

Perhaps this script will help. (This assumes that ALL tables have the 2 new fields, and they the names are the same in all tables.)

DECLARE
     @TotalTables   int
   , @CurrentRow    int
   , @TableName     varchar(100)
   , @SQL           nvarchar(1000)

SET @CurrentRow = 1

DECLARE @MyTables table
   (   RowID     int    IDENTITY
     , TableName varchar(100)
   )

INSERT INTO @MyTables
   SELECT TABLE_NAME
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  (   TABLE_TYPE = 'BASE TABLE'
          AND TABLE_NAME NOT LIKE 'dt%'
          )

SET @TotalTables = @@ROWCOUNT

WHILE @CurrentRow <= @TotalRows
   BEGIN

      SELECT @TableName = TableName
      FROM   @MyTables
      WHERE  RowID = @CurrentRow

      SET @SQL = 'UPDATE ' + @TableName + ' ' +
                   'SET Modify_Dt = NULL, Modify_vC = NULL'

      EXECUTE sp_executesql @SQL

      SET @CurrentRow = ( @CurrentRow + 1 )

   END

This is NOT tested. Test before using!!


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
----------------------------- end -----------------------

Thanks for your reply, but I think you misunderstood me or my
composition was wrong.

My goal is to erase suser_sname() and getdate() in this two column's
(modify_vC and modify_Dt) DEFAULT field.
Therefore when I insert a new record into  these 2 fields, they will
not be refreshed automatically for these two function's values
(suser_sname() and getdate())

Thanks,
Bálint

Bálint,

This query will produce the script that will drop default constraints for
these two columns on every table (I hope):

select 'alter table '+tbl.name+' drop constraint '+def.name+char(13)
from syscolumns c
inner join sysobjects tbl on c.id=tbl.id
inner join sysobjects def on c.cdefault=def.id
where c.name in ('modify_vC', 'modify_Dt')


HTH,

Dean


Thanks for your reply, but I think you misunderstood me or my
composition was wrong.

My goal is to erase suser_sname() and getdate() in this two column's
(modify_vC and modify_Dt) DEFAULT field.
Therefore when I insert a new record into  these 2 fields, they will
not be refreshed automatically for these two function's values
(suser_sname() and getdate())

Thanks,
Bálint

Hello, Dean.

It's strange. It runs without error and looks like it would be OK, but
nothing happens.
I made a test: I deleted DEFAULT fields by hand on a test database's
user tables (with "Design Table") except one ("def_Modozat" table) and
then ran the script. I got a good result...

alter table def_Modozat drop constraint DF_def_Modozat_Modosito_vC
alter table def_Modozat drop constraint DF_def_Modozat_Modositas_Dt

... however in fact nothing happened :-/
How can it be?

Thanks,
Bálint

What exactly do you expect to happen?

The script will drop the constraints, it will not change the data nor drop
the columns.

In addition to dropping the constraints you also need to update the data in
the columns.

update def_Modozat drop constraint
set modositas_Dt = null
, modosito_vC = null

It's strange. It runs without error and looks like it would be OK, but
nothing happens.
I made a test: I deleted DEFAULT fields by hand on a test database's
user tables (with "Design Table") except one ("def_Modozat" table) and
then ran the script. I got a good result...

alter table def_Modozat drop constraint DF_def_Modozat_Modosito_vC
alter table def_Modozat drop constraint DF_def_Modozat_Modositas_Dt

... however in fact nothing happened :-/
How can it be?

Thanks,
Bálint

Yes, I know.
I tried to say, that the suser_sname() and getdate() didn't dissapeared
at the DEFAULT property.
So, when I create a new record, modositas_Dt and modosito_vC still get
new value by these functions.


How can I put it into a loop. I tried this...

EXEC ('update ' + @table_name + ' drop constraint set modositas_Dt =
null, modosito_vC = null')

....but drop an error. (Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'drop'.)

Bálint

Bálint,

Sorry, I probably didn't make it clear enough..

The script from my previous post will not drop the default constraints - it
will only create the script you can use to drop the constraints. So, you
need to run it in Query Analyzer (make sure to check "Results in Text" on
the Query menu) and execute the script I've posted. It will produce the
script of form

alter table <table name> drop constraint <name of the default constrint>

one row for each table in the database that contains the modosito_vC or
modositas_Dt column. Then copy the result, paste it into another window, and
run it.

Test it before running it on the production database, please.

Hope it makes more sense now.

Dean


It's strange. It runs without error and looks like it would be OK, but
nothing happens.
I made a test: I deleted DEFAULT fields by hand on a test database's
user tables (with "Design Table") except one ("def_Modozat" table) and
then ran the script. I got a good result...

alter table def_Modozat drop constraint DF_def_Modozat_Modosito_vC
alter table def_Modozat drop constraint DF_def_Modozat_Modositas_Dt

... however in fact nothing happened :-/
How can it be?

Thanks,
Bálint

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.

end). It succeeded, but noticed that I made a mistake: set a Default value
for them. But they should had been empty :-/ <<
Show quoteHide quoteActually, unless I missed soemthign significant in the SOX act, it says
nothing anywhere about how and where your data is stored.  I believe you are
referring to the interpretation of the major auditing firms, who get paid by
the hour to audit your systems.  The more complicated their interpretation
of the law, the more hours they have to spend auditing to insure that every
detail of their interpretation is followed.

More to the point, chances are the original poster does not work in the
US. If that's the case, SOX considerations are just irrelevant.

OK, it could be, but I got these names to add (from my boss).
"Secondly" I mentioned that I am a newbie. I have to start somewhere.
And yes, I make (a lot of) stupid mistakes at the beginning.

"Third", it is certain that you're right. But... you are quite
offensive. I am a little bit disappointed. I thought this group exists
to help each other.

Don't let CELKO get to you too much, that is just his way.  Yes, he is often
offensive, but he also has a lot of good input.  Most folks here are not as
harsh as CELKO.

As with anything else, you just need to read his advice and decide whether
to use it or ignore it.

As a newbie you will make a lot of mistakes, but they are not stupid
mistakes, they are newbie mistakes that we all have made.  It takes several
years to know enough to make stupid mistakes.

Show quoteHide quote

Keep in mind that Joe is like a disciplinarian in high school.

He keeps reminding about boundaries, decorum, standards, acceptable
behavior, etc. We don't always like his manner, he can be gruff, even harsh.
His job is to keep everyone in line, no one really likes being watched or
even interacting with him, but he does have a purpose. He keeps order and
thwarts social devolution.

And sometimes, behind all the abrasiveness, sometimes he has good points.

(No, I am NOT a Joe apologist...)

Show quoteHide quote

Indexing question
Disable all constraints
Setting the Seed of an IDENTITY field on a temp table programatica
DAO, SQL Server 2005, and VB6
Pulling data from Access database into Intranet?