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
 Script Library
 Changing Column Length

Author  Topic 

Nazim
A custom title

1408 Posts

Posted - 2002-03-12 : 00:48:22
Going thru this thread http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13742 . i came up with this stored procedure which uses the information from systables and builds a dynamic alter table statement to modify user tables column lenght. but it wouldnt work if any column contains primary key constrains on it.

FORGIVE ME SQLTEAM for using Cursors .




create procedure upd_len(@mfindlen int,@mChangelen int)
as
declare c1 cursor for
select c.name Columname,t.name DataType,s.name TableName from syscolumns c
inner join
sysobjects s
on c.id=s.id
inner join systypes t
on c.xtype=t.xtype
where c.length=@mfindlen and s.xtype='U'
declare @Sql varchar(800)
declare @mColumnname varchar(128)
declare @mDatatype varchar(128)
declare @mtablename varchar(128)
begin
open c1

fetch next from c1 into @mcolumnname,@mDatatype,@mtablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql='alter table '+@mtablename+ ' alter column ' + @mcolumnname + ' '+@mDatatype +'('+ltrim(@mChangelen)+')'
print @sql
exec(@sql)
fetch next from c1 into @mcolumnname,@mDatatype,@mtablename
end
close c1
deallocate c1
end
go



eg usage
exec upd_len 10,12
will change the column lenght of all tables from 10 to 12.

--------------------------------------------------------------




Edited by - Nazim on 03/12/2002 00:56:37
   

- Advertisement -