包含数据长度的NAME函数
我正在考虑创建格式的功能
FULL_TYPE_NAME(type_id, max_length)
它以字符串格式返回数据类型和长度，例如：
FULL_TYPE_NAME (231,-1)
会返回：
nvarchar(max)
在我这样做之前，我想检查一下tsql是否已经有了这样一个函数（我还没有找到）或者是否有某种灵魂已经准备好了，我可以使用它。 如果没有，那么我会写一个并在这里发布。
提前致谢。
一个粗略的开始会是这样的：
CREATE FUNCTION udf_GetDataTypeAsString
(
@user_type_id INT ,
@Length INT
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @ReturnStr VARCHAR(50)
IF @Length = -1
SELECT @ReturnStr = UPPER(name) + '(MAX)'
FROM sys.types
WHERE user_type_id = @user_type_id
ELSE
SELECT @ReturnStr = UPPER(name) + '(' + CONVERT(VARCHAR, @Length) + ')'
FROM sys.types
WHERE user_type_id = @user_type_id
RETURN @ReturnStr
END
GO
SELECT dbo.udf_GetDataTypeAsString(167, -1)
--#### Returns VARCHAR(MAX)
SELECT dbo.udf_GetDataTypeAsString(231, 24)
--#### Returns NVARCHAR(24)
请注意，这仅适用于char数据类型，并且只能处理长度，如果要使用精度（小数等），则需要实现更多逻辑。
另外，您可能希望添加验证，以允许某些用户类型只允许-1长度
（为了好奇 - 你为什么要这样做？）
这是我的功能。 感谢HeavenCore的起点
CREATE FUNCTION dbo.full_type_name (
@User_Type_Id int,
@Length int)
RETURNS varchar (50)
AS
BEGIN
DECLARE @Returnstr varchar (50) ;
--Handle invalid values for @Length
IF (@Length = 0 OR @Length < -1 OR @Length > 8016 OR @Length IS NULL)
BEGIN
SET @Returnstr = NULL;
END;
ELSE
BEGIN
SELECT @Returnstr = TYPE_NAME (@User_Type_Id) ;
--unicode characters occupy two bytes
IF ((@Returnstr = 'nvarchar' OR @Returnstr = 'nchar') AND @Length > 0)
BEGIN
SET @Length = (@Length / 2);
END;
SELECT @Returnstr = @Returnstr + CASE
WHEN @Returnstr LIKE '%varchar' AND @Length < 0
THEN('(' + 'max' + ')')
WHEN @Returnstr LIKE '%char' AND @Length >= 0
THEN ('(' + CAST(@Length AS varchar + ')')
ELSE ''
END;
END;
RETURN @Returnstr;
END;
我打了电话
Select name,
user_type_id,
max_length,
dbo.full_type_name(ty.user_type_id, ty.max_length) as [full_type_name]
from sys.types as ty
测试它（仅限视觉检查）任何有关改进的建议都非常感谢
这是我最后的功能。 我认为它涵盖了一切，但随时纠正我。 我稍后会发布我的测试脚本
CREATE FUNCTION dbo.full_type_name (
@User_Type_Id int,
@Length smallint = NULL,
@Precision tinyint = NULL,
@Scale tinyint = NULL)
RETURNS varchar (50)
AS
BEGIN
DECLARE
@Returnstr varchar (50) = NULL,
@True bit = 1,
@False bit = 0,
@Params_Ok bit = 1;
SELECT @Params_Ok = CASE
--non unicode text
WHEN ((@User_Type_Id = 165 OR @User_Type_Id = 167
OR @User_Type_Id = 173 OR @User_Type_Id = 175)
AND ((@Length < -1) OR (@Length = 0) OR (@Length > 8000))) THEN @False
--unicode text
WHEN ((@User_Type_Id = 231 OR @User_Type_Id = 239)
AND ((@Length < -1) OR (@Length = 0) OR (@Length > 4000))) THEN @False
--decimal and numeric
WHEN ((@User_Type_Id = 106 OR @User_Type_Id = 108)
AND (((@Precision IS NULL) AND (@Scale IS NOT NULL))
OR ((@Precision IS NOT NULL) AND (@Scale IS NULL))
OR (@Precision <=0) OR (@Scale <0)
OR (@Precision <= @Scale))) THEN @False
--float
WHEN ((@User_Type_Id = 62) AND ((@Precision <= 0)
OR (@Precision > 53))) THEN @False
--time, datetime2 and datetimeoffset
WHEN ((@User_Type_Id BETWEEN 41 AND 43)
AND ((@Precision < 0) OR (@Precision > 7)))
THEN @False
END;
IF(@Params_Ok = @False)
BEGIN
RETURN NULL;
END;
SELECT @Returnstr = CASE
WHEN(@User_Type_Id = 129)THEN 'geometry'
WHEN(@User_Type_Id = 130)THEN 'geography'
ELSE TYPE_NAME (@User_Type_Id)
END;
--nvarchar and nchar characters occupy two bytes
IF ((@Returnstr = 'nvarchar' OR @Returnstr = 'nchar') AND @Length > 0)
BEGIN
SET @Length = (@Length / 2);
END;
SELECT @Returnstr = @Returnstr + CASE
WHEN ((@Returnstr LIKE '%varchar' OR @Returnstr = 'varbinary')
AND @Length < 0)
THEN('(' + 'max' + ')')
WHEN (((@Returnstr LIKE '%char') OR (@Returnstr LIKE '%binary'))
AND @Length >= 0)
THEN ('(' + CAST(@Length AS varchar) + ')')
WHEN ((@Returnstr = 'decimal' OR @Returnstr = 'numeric')
AND @Precision IS NOT NULL)
THEN ('(' + CAST(@Precision AS varchar) + ','
+ CAST(@Scale AS varchar) + ')')
WHEN (@Returnstr = 'float' AND @Precision IS NOT NULL)
THEN ('(' + CAST(@Precision AS varchar) + ')')
--time, datetime2 and datetimeoffset
WHEN ((@User_Type_Id BETWEEN 41 AND 43)
AND (@Precision IS NOT NULL))
THEN ('(' + CAST(@Precision AS varchar) + ')')
ELSE ''
END;
RETURN @Returnstr;
END;