CREATE FUNCTION dbo.GetColumnType (@TypeName SYSNAME,
@MaxLength SMALLINT,
@Precision TINYINT,
@Scale TINYINT,
@Collation SYSNAME,
@DBCollation SYSNAME)
RETURNS TABLE
AS
RETURN
SELECT CAST(CASE WHEN @TypeName IN ('char', 'varchar')
THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
ELSE CAST(@MaxLength AS VARCHAR)
END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
ELSE ''
END
WHEN @TypeName IN ('nchar', 'nvarchar')
THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
ELSE CAST(@MaxLength / 2 AS VARCHAR)
END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
ELSE ''
END
WHEN @TypeName IN ('binary', 'varbinary') THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
ELSE CAST(@MaxLength AS VARCHAR)
END + ')'
WHEN @TypeName IN ('bigint', 'int', 'smallint', 'tinyint') THEN @TypeName
WHEN @TypeName IN ('datetime2', 'time', 'datetimeoffset') THEN @TypeName + '(' + CAST (@Scale AS VARCHAR) + ')'
WHEN @TypeName IN ('numeric', 'decimal') THEN @TypeName + '(' + CAST(@Precision AS VARCHAR) + ', ' + CAST(@Scale AS VARCHAR) + ')'
ELSE @TypeName
END AS VARCHAR(256)) AS ColumnType