March 12, 2004 at 6:05 am
i have got 80 tables, now i have to change the datatypes of char to varchar if char length is more than one
plz suggest the T-Sql script for that.
March 12, 2004 at 6:15 am
select 'alter table ' + o.name + ' alter column '+ c.name + ' varchar(' + convert(varchar, c.length ) + ') '
from syscolumns c inner join sysobjects o on c.id = o.id
where c.xtype = 175
and o.type = 'u'
and c.length > 1
March 12, 2004 at 7:17 am
I would first think about the actual need to change everything.
VARCHAR(n) makes sense when you expect significant differences in the length of the information to store. If that is not the case, the use of CHAR(n) might make sense.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 12, 2004 at 8:43 am
I would also consider changing the anything greater than 1 to VarChar.
I would have to check the details, I heard this a long time ago in a Seminar from Kalen Delany, but a Varchar has 18 bytes of overhead. So don't quote me.
So as Frank said, unless you are expecting significant variation in length or the field is > ?? bytes, don't change them.
I generally have used > 10 before I consider VarChar, Kalen's comments make me lean toward > about 20 chars.
KlK
March 14, 2004 at 11:55 pm
Actually i am integration GP with .Net thats why i have to go this ,one way could be like i generate the script and run the script at client end and before running the script of db i make these changes so db will be created with modified script.
if i apply this what could be the best way to make changes in sql script before creating db.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply