Datatype value change - 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

General SQL Server Forums

New to SQL Server Programming

Datatype value change

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2010-10-01 : 00:12:34

Hi,

I want to reduce the field size from Varchar(40) to Varchar(30).
I do not want to do it manually... Can it possible through query?

thanks

matty
Posting Yak Master

161 Posts

Posted - 2010-10-01 : 00:39:44

ALTER TABLE tablename ALTER COLUMN columnname varchar(30)

asm
Posting Yak Master

140 Posts

Posted - 2010-10-01 : 03:19:17

I want i table's column change...
it may be 200+ tables

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 04:38:14

Is it always exact the same columnname?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 04:54:34

[code]declare @tables table(tablename varchar(255), changed char(1))
declare @sql varchar(max)
declare @tname varchar(255)
set @sql=''

insert @tables
select
t.name as tablename,
'N'
from sys.objects t
join sys.columns s
on s.Object_Id = t.Object_Id
where s.name ='HereComesYourColumnname'

while (select count(*) from @tables where changed='N') > 0
begin
select top 1 @tname=tablename from @tables where changed='N'
set @sql='alter table '+@tname+' alter column HereComesYourColumnname varchar(30)'
exec (@sql)
set @sql=''
update @tables set changed='Y' where tablename=@tname
end
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 05:29:41

Have you checked that this is safe? That you don't have any values in those columns more than 30 characters?

Also -- what's the point? you won't save any storage this way. any VARCHAR is 2 + number of characters bytes so.....

why do this?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

asm
Posting Yak Master

140 Posts

Posted - 2010-10-05 : 04:35:36

columnname is different...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 04:41:18

That's bad luck...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 06:05:51