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