首页 > 代码库 > 模拟实现SQL Server字段列显示的数据类型

模拟实现SQL Server字段列显示的数据类型

本文目录列表:
1、SQL Server表设计视图中的数据类型列展示效果
2、模拟实现类似的数据类型显示效果
3、测试效果
4、总结语
5、参考清单列表
 
1、SQL Server表设计视图中的数据类型列展示效果
 
在SQL Server 2012的表设计视图中可以看到如下图的效果:
技术分享
技术分享
如上图所示红色矩形框圈住的数据类型列中展示的效果,特别针对用户定义数据类型也显示了其基本数据类型。
 
这种针对字段列显示的数据类型效果很直接,很容易看到其数据类类型是什么,甚至看能看到精度或小数位,最重要的是显示了用户定义数据类型的基本数据类型。有时候在数据库设计中往往定义通用的用户定义数据类型,如身份证,手机号码,性别,名称等等通用的用户定义数据类型。
 
有时候将表字段列导出来保存doc、excel以及html,针对字段列的数据类型显示往往需要4个字段列来展示,如下图所示:
技术分享
技术分享
上图所示的就不太很直观的看出该字段的数据类型的详细信息,特别该字段列的数据类型为用户定义的数据类型时,更无从看出来它的基本数据类型。
 
注意:在SQL Server中sysname数据定义数据类型默认是系统定义的,在表设计视图中找不到该该数据类型,只能通过DDL命令来定义属于sysname的字段列。
 
2、模拟实现类似的数据类型显示效果
 
为了将字段列显示的数据类型展示的更有清晰简单,本人就模拟SQL Server 表设计视图中的字段列的展示效果的实现功能。
 
实现一个标量函数,支持将基本数据类型大小写的功能,具体的T-SQL代码如下:
IF OBJECT_ID(N[dbo].[ufn_GetDisplayDataTypeName], FN) IS NOT NULLBEGIN    DROP FUNCTION [dbo].[ufn_GetDisplayDataTypeName];ENDGO --==================================-- 功能: 获取字段列显示的数据类型名称-- 说明: 支持用户定义类型,可以运行于SQL Server 2005+-- 创建: yyyy-MM-dd hh:mm-hh:mm XXX 创建内容描述-- 修改: yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述--==================================CREATE FUNCTION [dbo].[ufn_GetDisplayDataTypeName](          @intUserTypeID AS INT                -- 用户类型ID    ,@sntMaxLength AS SMALLINT            -- 最大字节长度    ,@tntPrecision AS TINYINT            -- 精度    ,@tntScale AS TINYINT               -- 小数位    ,@bitIsUpperDisplay AS BIT = 0        -- 是否大写显示(默认小写显示)) RETURNS NVARCHAR(270)ASBEGIN    SET @bitIsUpperDisplay = ISNULL(@bitIsUpperDisplay, 0);     DECLARE         @nvcDataTypeName AS NVARCHAR(128)        ,@nvcBaseDataTypeName AS NVARCHAR(128)        ,@nvcSuffixDisplayName AS NVARCHAR(14)    SELECT         @nvcDataTypeName = N‘‘        ,@nvcBaseDataTypeName = N‘‘        ,@nvcSuffixDisplayName = N‘‘     SELECT         @nvcDataTypeName = [name]        ,@nvcBaseDataTypeName = (CASE WHEN (@nvcDataTypeName = Nsysname OR [is_user_defined] = 1) THEN TYPE_NAME([system_type_id]) ELSE @nvcDataTypeName END)    FROM         [sys].[types]    WHERE         [user_type_id] = @intUserTypeID;    SET @nvcDataTypeName = (CASE WHEN @nvcDataTypeName <> @nvcBaseDataTypeName THEN @nvcDataTypeName + N: ELSE N‘‘ END);    SET @nvcBaseDataTypeName = CASE @bitIsUpperDisplay WHEN 1 THEN UPPER(@nvcBaseDataTypeName) ELSE @nvcBaseDataTypeName END;    SET @nvcSuffixDisplayName = (CASE        WHEN @nvcBaseDataTypeName = Nchar THEN QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N))        WHEN @nvcBaseDataTypeName = Nvarchar THEN (CASE WHEN @sntMaxLength = -1 THEN N(MAX)  ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N)) END)        WHEN @nvcBaseDataTypeName = Nnchar THEN QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N))        WHEN @nvcBaseDataTypeName = Nnvarchar THEN (CASE WHEN @sntMaxLength = -1 THEN N(MAX)  ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N)) END)        WHEN @nvcBaseDataTypeName = Nbinary THEN QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N))        WHEN @nvcBaseDataTypeName = Nvarbinary THEN (CASE WHEN @sntMaxLength = -1 THEN N(MAX)  ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N)) END)        WHEN @nvcBaseDataTypeName IN (Nnumeric, Ndecimal) THEN QUOTENAME(RTRIM(CAST(@tntPrecision AS VARCHAR(4))) + N, + RTRIM(CAST(@tntScale AS VARCHAR(4))), N))        WHEN @nvcBaseDataTypeName IN (Ntime, Ndatetime2, Ndatetimeoffset) THEN QUOTENAME(RTRIM(CAST(@tntScale AS VARCHAR(4))), N))        ELSE N‘‘ END);     RETURN @nvcDataTypeName + @nvcBaseDataTypeName + @nvcSuffixDisplayName;ENDGO

 

3、测试效果
 
演示上面标量函数的展示效果,以下定义若干用户定义数据类型来验证标量函数dbo.ufn_GetDisplayDataTypeName的效果。
 
以下准备要验证效果的数据如下:

CREATE TYPE [dbo].[fd_udt_digest] FROM [NVARCHAR](255) NULLGO CREATE TYPE [dbo].[fd_udt_id] FROM [CHAR](15) NOT NULLGO CREATE TYPE [dbo].[fd_udt_username] FROM [NVARCHAR](20) NULLGO CREATE TYPE [dbo].[UFemail] FROM [VARCHAR](100) NULLGO CREATE TYPE [dbo].[UFFlag] FROM [CHAR](1) NULLGO CREATE TYPE [dbo].[UFhyperlink] FROM [VARCHAR](100) NULLGO CREATE TYPE [dbo].[UFMedia] FROM [IMAGE] NULLGO CREATE TYPE [dbo].[UFreference] FROM [VARCHAR](30) NULLGO CREATE TYPE [dbo].[UFtext] FROM [NTEXT] NULLGO CREATE TYPE [dbo].[UFUID] FROM [UNIQUEIDENTIFIER] NULLGO CREATE TYPE [dbo].[userdecimal] FROM [DECIMAL](28, 6) NOT NULLGO CREATE TYPE [dbo].[udtProduct] AS TABLE (     ProductID INT NOT NULL    ,UnitPrice DECIMAL(9, 2) NOT NULL     ,Quantity INT NOT NULL    PRIMARY KEY ([ProductID] ASC))GO CREATE TABLE [dbo].[ScaleDataTypeTable](    [ColDecimal] [NUMERIC](18, 2) NULL,    [ColNumeric] [NUMERIC](9, 2) NULL,    [ColFloat] [FLOAT] NULL,    [ColReal] [REAL] NULL,    [ColTime] [TIME](5) NOT NULL,    [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] HIERARCHYID NULL,    [Col13] GEOMETRY NULL,    [Col14] GEOGRAPHY NOT NULL,    [Col15] CHAR(10) NOT NULL,    [Col16] VARCHAR(25) NOT NULL,    [Col16Max] VARCHAR(MAX) NOT NULL,    [Col17] NCHAR(16) NOT NULL,    [Col18] NVARCHAR(32) NOT NULL,    [Col18Max] NVARCHAR(MAX) NOT NULL,    [ColBigint] BIGINT NOT NULL,    [ColInt] INT NOT NULL,    [ColSmallint] INT NOT NULL,    [ColTinyint] TINYINT NOT NULL,    [ColBit] BIT NOT NULL,    [Col19] DECIMAL(9,7) NOT NULL,    [Col20] MONEY NOT NULL,    [Col21] SMALLMONEY NOT NULL,    [Col22] TIMESTAMP NOT NULL,        [Col24] UNIQUEIDENTIFIER NOT NULL,    [Col25] IMAGE NOT NULL,    [Col26] TEXT NOT NULL,    [Col27] NTEXT NOT NULL,    [Col28] BINARY(8) NOT NULL,    [Col29] VARBINARY(8) NOT NULL,    [Col29Max] VARBINARY(MAX) NOT NULL,    [ColMxml] XML NULL,    [Col30] DATE NOT NULL,    [Col31] DATETIME NOT NULL,    [Col32] SMALLDATETIME NOT NULL) ON [PRIMARY]; GO CREATE TABLE [dbo].[UpdateDataTable](    UpdateDataTableId INT NOT NULL,    [Col23] ROWVERSION NOT NULL,    [Colname] sysname NOT NULL) 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] AS T1    INNER JOIN [sys].[all_objects] AS T2        ON [T1].[object_id] = [T2].[object_id]WHERE     [T2].[name] IN (NScaleDataTypeTable, NUpdateDataTable)    --AND EXISTS (SELECT 1 FROM [sys].[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] AS T1    INNER JOIN [sys].[all_objects] AS T2        ON [T1].[object_id] = [T2].[object_id]WHERE     [T2].[name] IN (NScaleDataTypeTable, NUpdateDataTable)    --AND EXISTS (SELECT 1 FROM [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、参考清单列表
无。

模拟实现SQL Server字段列显示的数据类型