Adam,

He had it on his site - #18

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

CREATE FUNCTION dbo.udf_SQL_DataTypeString (

@BaseDataType nvarchar(128) -- base name like int, numeric

, @Character_Maximum_Length int

, @Numeric_Precision int

, @Numeric_Scale int

) RETURNS nvarchar(24) -- Data type name like 'numeric (15, 3)'

WITH SCHEMABINDING

/*

* Returns a data type with full length and precision information

* based on fields originally queried from

* INFORMATION_SCHEMA.ROUTINES or from SQL_VARIANT_PROPERTIES.

* This function is intended to help when reporting on functions

* and about data.

*

* Example:

SELECT ROUTINE_NAME as [Function]

, dbo.udf_SQL_DataTypeString (Data_Type

, Character_Maximum_Length, Numeric_Precision, Numeric_Scale)

as [Data Type] FROM information_schema.routines

WHERE ROUTINE_TYPE='FUNCTION'

*

* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com

* You may use this function in any of your SQL Server databases

* including databases that you sell, so long as they contain

* other unrelated database objects. You may not publish this

* UDF either in print or electronically.

* Published in T-SQL UDF of the Week Newsletter Vol 1 #18

http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm

****************************************************************/

AS BEGIN

RETURN LTRIM(RTRIM(@BaseDataType))

+ CASE WHEN @BaseDataType in ('char', 'varchar'

, 'nvarchar', 'nchar')

THEN '('

+ CONVERT (varchar(4)

, @Character_Maximum_Length)

+ ')'

WHEN @BaseDataType in ('numeric', 'decimal')

THEN '('

+ Convert(varchar(4), @Numeric_Precision)

+ ' ' + convert(varchar(4), @Numeric_scale)

+ ')'

ELSE '' -- empty string

END

END

GRANT EXEC, REFERENCES ON dbo.udf_SQL_DataTypeString to [PUBLIC]

GO