模拟实现SQLServer字段列显示的数据类型
本文目录列表：
1、SQLServer表设计视图中的数据类型列展示效果
2、模拟实现类似的数据类型显示效果
3、测试效果
4、总结语
5、参考清单列表
?
1、SQLServer表设计视图中的数据类型列展示效果
?
在SQLServer2012的表设计视图中可以看到如下图的效果：
如上图所示红色矩形框圈住的数据类型列中展示的效果，特别针对用户定义数据类型也显示了其基本数据类型。
?
这种针对字段列显示的数据类型效果很直接，很容易看到其数据类类型是什么，甚至看能看到精度或小数位，最重要的是显示了用户定义数据类型的基本数据类型。有时候在数据库设计中往往定义通用的用户定义数据类型，如身份证，手机号码，性别，名称等等通用的用户定义数据类型。
?
有时候将表字段列导出来保存doc、excel以及html，针对字段列的数据类型显示往往需要4个字段列来展示，如下图所示：
上图所示的就不太很直观的看出该字段的数据类型的详细信息，特别该字段列的数据类型为用户定义的数据类型时，更无从看出来它的基本数据类型。
?
注意：在SQLServer中sysname数据定义数据类型默认是系统定义的，在表设计视图中找不到该该数据类型，只能通过DDL命令来定义属于sysname的字段列。
?
2、模拟实现类似的数据类型显示效果
?
为了将字段列显示的数据类型展示的更有清晰简单，本人就模拟SQLServer表设计视图中的字段列的展示效果的实现功能。
?
实现一个标量函数，支持将基本数据类型大小写的功能，具体的T-SQL代码如下：
IFOBJECT_ID(N''[dbo].[ufn_GetDisplayDataTypeName]'',''FN'')ISNOTNULL
BEGIN
DROPFUNCTION[dbo].[ufn_GetDisplayDataTypeName];
END
GO
--==================================
--功能:获取字段列显示的数据类型名称
--说明:支持用户定义类型，可以运行于SQLServer2005+
--创建:yyyy-MM-ddhh:mm-hh:mmXXX创建内容描述
--修改:yyyy-MM-ddhh:mm-hh:mmXXX修改内容描述
--==================================
CREATEFUNCTION[dbo].[ufn_GetDisplayDataTypeName]
(
@intUserTypeIDASINT--用户类型ID
,@sntMaxLengthASSMALLINT--最大字节长度
,@tntPrecisionASTINYINT--精度
,@tntScaleASTINYINT--小数位
,@bitIsUpperDisplayASBIT=0--是否大写显示（默认小写显示）
)RETURNSNVARCHAR(270)
AS
BEGIN
SET@bitIsUpperDisplay=ISNULL(@bitIsUpperDisplay,0);
DECLARE
@nvcDataTypeNameASNVARCHAR(128)
,@nvcBaseDataTypeNameASNVARCHAR(128)
,@nvcSuffixDisplayNameASNVARCHAR(14)
SELECT
@nvcDataTypeName=N''''
,@nvcBaseDataTypeName=N''''
,@nvcSuffixDisplayName=N''''
SELECT
@nvcDataTypeName=[name]
,@nvcBaseDataTypeName=(CASEWHEN(@nvcDataTypeName=N''sysname''OR[is_user_defined]=1)THENTYPE_NAME([system_type_id])ELSE@nvcDataTypeNameEND)
FROM
[sys].[types]
WHERE
[user_type_id]=@intUserTypeID;
SET@nvcDataTypeName=(CASEWHEN@nvcDataTypeName@nvcBaseDataTypeNameTHEN@nvcDataTypeName+N'':''ELSEN''''END);
SET@nvcBaseDataTypeName=CASE@bitIsUpperDisplayWHEN1THENUPPER(@nvcBaseDataTypeName)ELSE@nvcBaseDataTypeNameEND;
SET@nvcSuffixDisplayName=(CASE
WHEN@nvcBaseDataTypeName=N''char''THENQUOTENAME(RTRIM(CAST(@sntMaxLengthASVARCHAR(4))),N'')'')
WHEN@nvcBaseDataTypeName=N''varchar''THEN(CASEWHEN@sntMaxLength=-1THENN''(MAX)''ELSEQUOTENAME(RTRIM(CAST(@sntMaxLengthASVARCHAR(4))),N'')'')END)
WHEN@nvcBaseDataTypeName=N''nchar''THENQUOTENAME(RTRIM(CAST(@sntMaxLength/2ASVARCHAR(4))),N'')'')
WHEN@nvcBaseDataTypeName=N''nvarchar''THEN(CASEWHEN@sntMaxLength=-1THENN''(MAX)''ELSEQUOTENAME(RTRIM(CAST(@sntMaxLength/2ASVARCHAR(4))),N'')'')END)
WHEN@nvcBaseDataTypeName=N''binary''THENQUOTENAME(RTRIM(CAST(@sntMaxLengthASVARCHAR(4))),N'')'')
WHEN@nvcBaseDataTypeName=N''varbinary''THEN(CASEWHEN@sntMaxLength=-1THENN''(MAX)''ELSEQUOTENAME(RTRIM(CAST(@sntMaxLength/2ASVARCHAR(4))),N'')'')END)
WHEN@nvcBaseDataTypeNameIN(N''numeric'',N''decimal'')THENQUOTENAME(RTRIM(CAST(@tntPrecisionASVARCHAR(4)))+N'',''+RTRIM(CAST(@tntScaleASVARCHAR(4))),N'')'')
WHEN@nvcBaseDataTypeNameIN(N''time'',N''datetime2'',N''datetimeoffset'')THENQUOTENAME(RTRIM(CAST(@tntScaleASVARCHAR(4))),N'')'')
ELSEN''''END);
RETURN@nvcDataTypeName+@nvcBaseDataTypeName+@nvcSuffixDisplayName;
END
GO
?
3、测试效果
?
演示上面标量函数的展示效果，以下定义若干用户定义数据类型来验证标量函数dbo.ufn_GetDisplayDataTypeName的效果。
?
以下准备要验证效果的数据如下：
CREATETYPE[dbo].[fd_udt_digest]FROM[NVARCHAR](255)NULL
GO
CREATETYPE[dbo].[fd_udt_id]FROM[CHAR](15)NOTNULL
GO
CREATETYPE[dbo].[fd_udt_username]FROM[NVARCHAR](20)NULL
GO
CREATETYPE[dbo].[UFemail]FROM[VARCHAR](100)NULL
GO
CREATETYPE[dbo].[UFFlag]FROM[CHAR](1)NULL
GO
CREATETYPE[dbo].[UFhyperlink]FROM[VARCHAR](100)NULL
GO
CREATETYPE[dbo].[UFMedia]FROM[IMAGE]NULL
GO
CREATETYPE[dbo].[UFreference]FROM[VARCHAR](30)NULL
GO
CREATETYPE[dbo].[UFtext]FROM[NTEXT]NULL
GO
CREATETYPE[dbo].[UFUID]FROM[UNIQUEIDENTIFIER]NULL
GO
CREATETYPE[dbo].[userdecimal]FROM[DECIMAL](28,6)NOTNULL
GO
CREATETYPE[dbo].[udtProduct]ASTABLE
(
ProductIDINTNOTNULL
,UnitPriceDECIMAL(9,2)NOTNULL
,QuantityINTNOTNULL
PRIMARYKEY([ProductID]ASC)
)
GO
CREATETABLE[dbo].[ScaleDataTypeTable](
[ColDecimal][NUMERIC](18,2)NULL,
[ColNumeric][NUMERIC](9,2)NULL,
[ColFloat][FLOAT]NULL,
[ColReal][REAL]NULL,
[ColTime][TIME](5)NOTNULL,
[ColDateTime2][DATETIME2](3)NULL,
[ColDateTimeOffset][DATETIMEOFFSET](5)NULL,
[ColSqlVariant][SQL_VARIANT]NULL,
[ColMoney][MONEY]NULL,
[ColSmallMoney][SMALLMONEY]NULL,
[Col1][dbo].[fd_udt_digest]NULL,
[Col2][dbo].[fd_udt_id]NULL,
[Col3][dbo].[fd_udt_username]NULL,
[Col4][dbo].[UFemail]NULL,
[Col5][dbo].[UFFlag]NULL,
[Col6][dbo].[UFhyperlink]NULL,
[Col7][dbo].[UFMedia]NULL,
[Col8][dbo].[UFreference]NULL,
[Col9][dbo].[UFtext]NULL,
[Col10][dbo].[UFUID]NULL,
[Col11][dbo].[userdecimal]NULL,
[Col12]HIERARCHYIDNULL,
[Col13]GEOMETRYNULL,
[Col14]GEOGRAPHYNOTNULL,
[Col15]CHAR(10)NOTNULL,
[Col16]VARCHAR(25)NOTNULL,
[Col16Max]VARCHAR(MAX)NOTNULL,
[Col17]NCHAR(16)NOTNULL,
[Col18]NVARCHAR(32)NOTNULL,
[Col18Max]NVARCHAR(MAX)NOTNULL,
[ColBigint]BIGINTNOTNULL,
[ColInt]INTNOTNULL,
[ColSmallint]INTNOTNULL,
[ColTinyint]TINYINTNOTNULL,
[ColBit]BITNOTNULL,
[Col19]DECIMAL(9,7)NOTNULL,
[Col20]MONEYNOTNULL,
[Col21]SMALLMONEYNOTNULL,
[Col22]TIMESTAMPNOTNULL,
[Col24]UNIQUEIDENTIFIERNOTNULL,
[Col25]IMAGENOTNULL,
[Col26]TEXTNOTNULL,
[Col27]NTEXTNOTNULL,
[Col28]BINARY(8)NOTNULL,
[Col29]VARBINARY(8)NOTNULL,
[Col29Max]VARBINARY(MAX)NOTNULL,
[ColMxml]XMLNULL,
[Col30]DATENOTNULL,
[Col31]DATETIMENOTNULL,
[Col32]SMALLDATETIMENOTNULL
)ON[PRIMARY];
GO
CREATETABLE[dbo].[UpdateDataTable]
(
UpdateDataTableIdINTNOTNULL,
[Col23]ROWVERSIONNOTNULL,
[Colname]sysnameNOTNULL
)ON[PRIMARY];
GO
?
基本数据类型小写展示的效果的T-SQL代码和效果截图如下：
SELECT
T2.[name]AS[table_name]
,[T1].[name]AS[column_name]
,TYPE_NAME([T1].[user_type_id])AS[column_datatype]
,[dbo].[ufn_GetDisplayDataTypeName]([T1].[user_type_id],[T1].[max_length],[T1].[precision],[T1].[scale],0)AS[column_display_datatype]
,[T1].[max_length]
,[T1].[precision]
,[T1].[scale]
FROM
[sys].[all_columns]AST1
INNERJOIN[sys].[all_objects]AST2
ON[T1].[object_id]=[T2].[object_id]
WHERE
[T2].[name]IN(N''ScaleDataTypeTable'',N''UpdateDataTable'')
--ANDEXISTS(SELECT1FROM[www.visa158.com].[types]WHERE([name]=N''sysname''OR[is_user_defined]=1)AND[user_type_id]=[T1].[user_type_id]);
GO?
?
基本数据类型大写展示的效果的T-SQL代码和效果截图如下：
SELECT
T2.[name]AS[table_name]
,[T1].[name]AS[column_name]
,TYPE_NAME([T1].[user_type_id])AS[column_datatype]
,[dbo].[ufn_GetDisplayDataTypeName]([T1].[user_type_id],[T1].[max_length],[T1].[precision],[T1].[scale],1)AS[column_display_datatype]
,[T1].[max_length]
,[T1].[precision]
,[T1].[scale]
FROM
[sys].[all_columns]AST1
INNERJOIN[sys].[all_objects]AST2
ON[T1].[object_id]=[T2].[object_id]
WHERE
[T2].[name]IN(N''ScaleDataTypeTable'',N''UpdateDataTable'')
--ANDEXISTS(SELECT1FROM[sys].[types]WHERE([name]=N''sysname''OR[is_user_defined]=1)AND[user_type_id]=[T1].[user_type_id]);
GO
?
?
4、总结语
?
这个功能刚开始自己使用了2个标量函数，针对sys.types每次查询最多调用四次；第二版本则集中到一个标量函数中，还是针对sys.types每次查询最多调用4次；第三版则重构和优化为sys.types每次查询最多2次；第4版本继续重构代码将函数返回的结果分为三个部分的联结，这样重构更简单明了；最后一版本增加了针对基本数据类型的大小写功能。
?
都说代码重构，这个功能的实现我确实感受到了重构和优化的效果，以上五个版本下来，有确实感觉到真正做好一件事情确实要好好的思考和动手实践的，不然很容易要么头脑迟钝要么眼高手低等不好的习惯养成的。近来一直在看数据库和商业智能方便的数据集，更印证了知道的越多，不知道和迫切想了解的就更多。如何将理论更好的指导实践以及实践更好的印证理论的可行性，需要更多的身体力行。
?
尽量要求自己每周至少发一篇博文和大家一起讨论学习进步。虽然自己也在转载和记录工作中或是自己动手实践的东西，有时候真的感觉时间有些不够用，需要更多的汲取其他优秀人员的技能和理论，才能谨慎的写下自己的所思所想，如果不周或认知不正确的地方也请各位海涵。近来学习到的东西太多，才发现之前发布的的东西有不正确的地方，慢慢地我根据现有的认知来更正。
?
5、参考清单列表
无。
|
|
