scrript to convert datatypes of multiple columns

,

generates an runs a select statement to convert all th olumns of a certain datatype in apirticular table to another datatype
:
eg all columns of type int to varchar etc

the sp takes three parameters namely the table name, source data type and detination data type

generates a select query ad executes it

eg: exec ConvertTableTypes 'ledgerjournaltrans','uniqueidentifier','varchar(36)'

create procedure ConvertTableTypes 
@tablename varchar(max),
@Coverttype varchar(max),
@convertToType varchar(max)
as 
declare @V_ColName varchar(max),@V_count int,@V_endVal int,@stmt varchar(max),@name nvarchar(max) 
print 'from :'+ @Coverttype
print 'to :'+ @convertToType
set @V_count=1
set @V_ColName=''
set @stmt=''

select @V_endVal=count(*) from(select ROW_NUMBER() over (Order by Col_Name) 'NUM',Table_Name,Col_Name,DataType from (select name 'Table_Name', id 'Tab_ID' from SYSOBJECTS where Xtype='U') A inner join (select Col_Name,DataType,Col_ID from(Select name 'Col_Name',xType 'Data_Type1',id 'Col_ID' from SYSCOLUMNS) C inner join (Select name 'DataType',Xtype 'Data_Type2' from SYSTYPES) D on C.Data_Type1=D.Data_Type2
 ) B on A.Tab_ID=B.Col_ID and datatype<>'sysname'  and Table_Name=@tablename and datatype like @Coverttype) E

while @V_count<=@V_endVal begin

select @V_ColName =@V_ColName+ Col_Name+',' from(select ROW_NUMBER() over (Order by Col_Name) 'NUM',Table_Name,Col_Name,DataType from (select name 'Table_Name', id 'Tab_ID' from SYSOBJECTS where Xtype='U') A inner join (select Col_Name,DataType,Col_ID from(Select name 'Col_Name',xType 'Data_Type1',id 'Col_ID' from SYSCOLUMNS) C inner join (Select name 'DataType',Xtype 'Data_Type2' from SYSTYPES) D on C.Data_Type1=D.Data_Type2
 ) B on A.Tab_ID=B.Col_ID and datatype<>'sysname'  and Table_Name=@tablename and datatype like @Coverttype) E where E.NUM=@V_count

set @V_count=@V_count+1
end

while @V_ColName<>'' begin
set @stmt = @stmt+ 'Cast('+ substring( @V_ColName,0,charindex(',',@V_ColName))+' as '+@convertToType+') as Converted_' +substring( @V_ColName,0,charindex(',',@V_ColName))+','
if charindex(',',@V_ColName)=0
break
set @V_ColName= SUBSTRING(@V_ColName, CHARINDEX(',', @V_colName) + 1, LEN(@V_ColName)) 
end

set @stmt=substring(@stmt,0,len(@stmt))

set @name ='select '+ @stmt+ ',* from ' + @tablename

print 'Generated SQL :' + @name

exec sp_executesql @name

Rate

Share

Share

Rate