你的位置:首页 > 软件开发 > 数据库 > 模拟实现SQL Server字段列显示的数据类型

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

发布时间:2016-09-21 10:00:11
本文目录列表:1、SQL Server表设计视图中的数据类型列展示效果2、模拟实现类似的数据类型显示效果3、测试效果4、总结语5、参考清单列表 1、SQL Server表设计视图中的数据类型列展示效果 在SQL Server 2012的表设计视图中可以看到如下图的效果:如 ...

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

本文目录列表: 模拟实现SQL Server字段列显示的数据类型模拟实现SQL Server字段列显示的数据类型3、测试效果
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,  [ColM] 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
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
模拟实现SQL Server字段列显示的数据类型

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

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。