模擬實現SQL Server欄位列顯示的數據類型

来源:http://www.cnblogs.com/dzy863/archive/2016/09/21/5891548.html
-Advertisement-
Play Games

本文目錄列表： 1、SQL Server表設計視圖中的數據類型列展示效果 2、模擬實現類似的數據類型顯示效果 3、測試效果 4、總結語 5、參考清單列表 本文目錄列表： 1、SQL Server表設計視圖中的數據類型列展示效果 2、模擬實現類似的數據類型顯示效果 3、測試效果 4、總結語 5、參考清 ...


本文目錄列表： 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 NULL
BEGIN
    DROP FUNCTION [dbo].[ufn_GetDisplayDataTypeName];
END
GO
 
--==================================
-- 功能: 獲取欄位列顯示的數據類型名稱
-- 說明: 支持用戶定義類型，可以運行於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)
AS
BEGIN
    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 = N'sysname' 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 = N'char' THEN QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N')')
        WHEN @nvcBaseDataTypeName = N'varchar' THEN (CASE WHEN @sntMaxLength = -1 THEN N'(MAX)'  ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N')') END)
        WHEN @nvcBaseDataTypeName = N'nchar' THEN QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N')')
        WHEN @nvcBaseDataTypeName = N'nvarchar' THEN (CASE WHEN @sntMaxLength = -1 THEN N'(MAX)'  ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N')') END)
        WHEN @nvcBaseDataTypeName = N'binary' THEN QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N')')
        WHEN @nvcBaseDataTypeName = N'varbinary' THEN (CASE WHEN @sntMaxLength = -1 THEN N'(MAX)'  ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N')') END)
        WHEN @nvcBaseDataTypeName IN (N'numeric', N'decimal') THEN QUOTENAME(RTRIM(CAST(@tntPrecision AS VARCHAR(4))) + N',' + RTRIM(CAST(@tntScale AS VARCHAR(4))), N')')
        WHEN @nvcBaseDataTypeName IN (N'time', N'datetime2', N'datetimeoffset') THEN QUOTENAME(RTRIM(CAST(@tntScale AS VARCHAR(4))), N')')
        ELSE N'' END);
 
    RETURN @nvcDataTypeName + @nvcBaseDataTypeName + @nvcSuffixDisplayName;
END
GO

 

3、測試效果
   演示上面標量函數的展示效果，以下定義若幹用戶定義數據類型來驗證標量函數dbo.ufn_GetDisplayDataTypeName的效果。   以下準備要驗證效果的數據如下： 
CREATE TYPE [dbo].[fd_udt_digest] FROM [NVARCHAR](255) NULL
GO
 
CREATE TYPE [dbo].[fd_udt_id] FROM [CHAR](15) NOT NULL
GO
 
CREATE TYPE [dbo].[fd_udt_username] FROM [NVARCHAR](20) NULL
GO
 
CREATE TYPE [dbo].[UFemail] FROM [VARCHAR](100) NULL
GO
 
CREATE TYPE [dbo].[UFFlag] FROM [CHAR](1) NULL
GO
 
CREATE TYPE [dbo].[UFhyperlink] FROM [VARCHAR](100) NULL
GO
 
CREATE TYPE [dbo].[UFMedia] FROM [IMAGE] NULL
GO
 
CREATE TYPE [dbo].[UFreference] FROM [VARCHAR](30) NULL
GO
 
CREATE TYPE [dbo].[UFtext] FROM [NTEXT] NULL
GO
 
CREATE TYPE [dbo].[UFUID] FROM [UNIQUEIDENTIFIER] NULL
GO
 
CREATE TYPE [dbo].[userdecimal] FROM [DECIMAL](28, 6) NOT NULL
GO
 
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 (N'ScaleDataTypeTable', N'UpdateDataTable')
    --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 (N'ScaleDataTypeTable', N'UpdateDataTable')
    --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、參考清單列表
 無。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SQL 函數
    sql函數 character函數 String 1.INITCAP 首字母大小 select initcap(address) address from student; select initcap('ni hao') from dual; --dual虛擬表 '數據' 列名-數據 2.LTRI ...
  • VS連接Access資料庫--連接字元串及執行查詢語句的方法（增刪改查，用戶名查重，根據用戶獲取密碼查詢）
    ACCESS數據的連接及語句執行操作，不難，久不用會生疏，每次都要找資料，乾脆自己整理下，記錄下來，需要的時候，直接查看，提高效率。也供初學者參考 1、連接字元串 public static string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Da ...
  • Mysql5.7服務下載安裝
    身處MySQL這個圈子，能夠切身地感受到大家對MySQL 5.7的期待和熱情，似乎每個人都迫不及待的想要瞭解、學習和使用MySQL 5.7。那麼，我們不禁要問，MySQL 5.7到底做了哪些改進，引入了哪些新功能，性能又提升了多少，能夠讓大家翹首以盼，甚至歡呼雀躍呢？ MySQL 5.7在諸多方面都 ...
  • Oracle學習總結
    Oracle day01 Oracle簡介及表的創建,增刪改查 安裝好之後,需要手動開啟的服務 : 右鍵我的電腦 管理 服務 OraclORCL 和 OracleListiner . 必須開啟. dos 底下輸入sqlplus啟動oracle,資料庫口令 : orcl 用戶名system密碼orcl ...
  • csharp: Oracle Stored Procedure DAL using ODP.NET
    paging : http://www.codeproject.com/Articles/44858/Custom-Paging-GridView-in-ASP-NET-Oracle http://dba-oracle.com/t_display_oracle_stored_procedures.h ...
  • 實現從Oracle增量同步數據到GreenPlum
    簡介： GreenPlum是一個基於PostgreSQL資料庫開發的MPP架構的資料庫倉庫，適用於OLAP系統，支持50PB(1PB=1000TB)級海量數據的存儲和處理。 背景： 目前有一個業務是需要將Oracle資料庫中的基礎數據增量同步到GreenPlum數據倉庫，便於進行數據分析和處理。 規 ...
  • sql將查詢的結果集一次性插入到表變數中
    sql代碼： 資料庫死鎖（查詢或其他陷入死迴圈） ...
  • 數據倉庫開發——Kettle使用示例
    Kettle是一個開園ETL工具，做數據倉庫用Spoon。然而網上找了很久沒有找到一個手把手教人的示例，於是自己學習的過程中把步驟都截圖保存下來了。步驟可能有點簡略，但是作為程式員肯定是能看懂的，僅供參考學習。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類