2009-09-25

Convert SQL types in destination to SQL type in source

Another little script I needed for today. Feel free to expand on the CASE statement for your own types (which I will do should I need to run this again).



/*
Will transform destination table's column types to match the source database's
(when column names match)
codesnippet:83d07e82-3866-4877-849f-0ae06322b9fb
*/


declare @destination varchar(50)
declare @source varchar(50)

---- Configuration -----
set @destination = 'PlanImport'
set @source = 'Plan'
------------------------

declare @i int
declare @count int
declare @sql varchar(8000)
declare @columnName varchar(8000)
declare @columnType varchar(8000)
declare @columns table (id int identity(1,1), columnName varchar(50), columnType varchar(50) )

-- insert source columns that are in destination
insert into @columns
select source.[Name],
(CASE source_types.[Name]
WHEN 'decimal' THEN
source_types.[Name] + '(' + cast(source.[precision] as varchar(50)) + ',' + cast(source.[scale] as varchar(50)) + ')'
WHEN 'int' THEN
source_types.[Name]
WHEN 'varchar' THEN
source_types.[Name] + '(' + cast(source.[max_length] as varchar(50)) + ')'
ELSE
source_types.[Name]
END) as [Name]
from sys.columns as source
join sys.columns as destination on destination.[name] = source.[name]
join sys.types source_types on source_types.system_type_id = source.system_type_id
where source.object_id = object_id(@source) and destination.object_id = object_id(@destination)

set @i = 1
set @count = (select max(id) from @columns)

while(@i <= @count)
begin
set @columnName = (SELECT [columnName] FROM @columns WHERE [ID] = @i)
set @columnType = (SELECT [columnType] FROM @columns WHERE [ID] = @i)

--select @columnName
set @sql = 'ALTER TABLE ' + @destination + ' ALTER COLUMN ' + @columnName + ' ' + @columnType

execute(@sql)

set @i = @i + 1
end

select 'Done!'


No comments: