Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Changing Column Length
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

A custom title

United Arab Emirates
1408 Posts

Posted - 03/12/2002 :  00:48:22  Show Profile  Reply with Quote
Going thru this thread . 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)
declare c1 cursor for
select Columname, DataType, TableName from syscolumns c
inner join
sysobjects s
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)
open c1

fetch next from c1 into @mcolumnname,@mDatatype,@mtablename
select @sql='alter table '+@mtablename+ ' alter column ' + @mcolumnname + ' '+@mDatatype +'('+ltrim(@mChangelen)+')'
print @sql
fetch next from c1 into @mcolumnname,@mDatatype,@mtablename
close c1
deallocate c1

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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000