|
Nazim
A custom title
United Arab Emirates
1408 Posts |
 Posted - 03/12/2002 : 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 |
|
Changing Column Length
