May 2, 2003 at 8:12 am
Was wondering if anyone had ever written or seen a utility to go through a database, find all fields that are CHAR, change the type to VARCHAR, then TRIM the fields.
If nothing's readily available, suggestions on an approach, maybe?
Metrics: 3 Databases, 100 tables each DB, over 3,500 fields to examine over the 100 tables. (Which is why it's not practical to do the conversion by hand...):
Background: We went through a wholesale 'rescue' of an old HP IMAGE database with very little time so we just used the DTS wizard to quickly upload table by table. The ODBC driver took all string fields in IMAGE and made them CHAR. Just scanning a few tables indicates the string data is at most 15% full - all the rest is stored blank spaces, making the storage requirement 6 or 7 times what it needs to be. While storage is cheap, this is bogging down backups & searches.
Ideas? T.I.A.
J.C.Michael
"You can never have too much reverb"
J.C.Michael
"You can never have too much reverb"
May 2, 2003 at 8:25 am
I once wrote scripts to do this.
At the moment I can only find the trim script:
select 'update ' + o.name + ' set ' + c.name + '=RTRIM(' + c.name + ')'
from syscolumns c, sysobjects o
where o.id=c.id
and c.xtype=167
and o.type='U'
order by o.name,c.name
This generates the script that does the real work. The alter table script can be generated in a similar way.
May 2, 2003 at 9:09 am
(deleted duplicate post)
Edited by - JCMichael on 05/02/2003 09:09:20 AM
J.C.Michael
"You can never have too much reverb"
May 2, 2003 at 9:09 am
(deleted duplicate post)
Edited by - JCMichael on 05/02/2003 09:09:55 AM
J.C.Michael
"You can never have too much reverb"
May 2, 2003 at 10:09 am
Great help, Thanks!
I think I can see how to generate the similar code for the ALTER TABLE - I know the xtype to search for is '175' and replace with '167'.
Just hadn't thought of the idea of generating update commands via a literal and variable string concatenation...clever!
Thanks again!
J.C.Michael
"You can never have too much reverb"
J.C.Michael
"You can never have too much reverb"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply