IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_columnType')
DROP FUNCTION ddf_columnType
GO
CREATE FUNCTION ddf_columnType
(@ColumnType as nvarchar(256),@CHARACTER_MAXIMUM_LENGTH int, @intPrecision int , @intscale int)
RETURNS varchar(20)
AS
BEGIN
declare @out varchar(20)
select @out =
case when @ColumnType = 'int' then 'int'
when @ColumnType = 'tinyint' then 'tinyint'
when @ColumnType = 'uniqueidentifier' then 'uniqueidentifier'
when @ColumnType = 'smallint' then 'smallint'
when @ColumnType = 'money' then 'money'
when @ColumnType = 'bit' then 'bit'
when @ColumnType = 'bit' then 'bit'
when @ColumnType = 'float' then 'float'
when @ColumnType = 'text' then 'text'
when @ColumnType in ('datetime') then 'datetime'
when @ColumnType in ('date') then 'date'
when @ColumnType in ('time') then 'time'
when @ColumnType = 'timestamp' then 'timestamp'
when @ColumnType = 'nvar' then 'nvar(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'nvarchar' then 'nvarchar(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'binary' then 'binary(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType in ('char') then 'char(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'varchar' and @CHARACTER_MAXIMUM_LENGTH = -1 then 'Varchar(MAX)'
when @ColumnType = 'varchar' then 'varchar(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType in ('numeric') then 'numeric(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
when @ColumnType in ('decimal') then 'decimal(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
else 'unk - ' + convert(varchar(20),@ColumnType)
end
return @out
END
GO