SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 03/12/2002 :  00:48:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000