Urgent! Table Default Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Urgent! Table Default Values

Author

Topic

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2005-02-02 : 10:58:05

I copied Tables, data over to SQL 7.0 from SQL 2000.
I see this morning that default values are missing in SQL 7.0 tables.
There has been lot of transaction done since yesterday.
Any suggestions how to deal with the situation?

Rick

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 11:38:45

Script out and create the defaults, and update the tables...

These scripts might help You,
bear in mind that maybe all the defaults do not apply for an update:

SELECT
	'ALTER TABLE ' + QUOTENAME(o.name) + ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(coms.id)) + ' DEFAULT' + coms.text + ' FOR ' + QUOTENAME(cols.name)
FROM
	sysobjects o
	JOIN syscolumns cols ON o.id = cols.id
	JOIN syscomments coms ON cols.cdefault = coms.id


SELECT
	'UPDATE ' + QUOTENAME(o.name) + ' SET ' + QUOTENAME(cols.name) + ' = ' + coms.text + ' WHERE ' + QUOTENAME(cols.name) + ' IS NULL'
FROM
	sysobjects o
	JOIN syscolumns cols ON o.id = cols.id
	JOIN syscomments coms ON cols.cdefault = coms.id

rockmoose

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2005-02-02 : 11:50:55

Thanks so much Rockmoose. With the update statement data which is already in the tables will also change to default Values? Dum Question....
Thanks for all your help:)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 12:02:45

It only updates the rows where the value IS NULL, it doesn't overwrite any existing data.
Of course, You should inspect and understand the sql code before You run it!

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources