Sql-Server

如何編寫使用者定義的表類型？

  • October 29, 2019

我可以 使用以下任一腳本獲取所有表類型的名稱和定義：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL
  DROP TABLE #RADHE

CREATE TABLE #RADHE
(
RADHE SYSNAME,
COLUMN_NAME SYSNAME,
TYPE_COLUMN SYSNAME,
PRIMARY KEY CLUSTERED (RADHE,COLUMN_NAME)
)

DECLARE @sql nvarchar(max) = N'', 
 @stub nvarchar(max) = N'SELECT [RADHE]=N''$--RADHE--$'', 
  COLUMN_NAME=name, TYPE_COLUMN=system_type_name
  FROM sys.dm_exec_describe_first_result_set(''DECLARE 
  @tvp $--RADHE--$; SELECT * FROM @tvp;'',null,null)
  ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--RADHE--$', 
 QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

INSERT INTO #RADHE 
EXEC sys.sp_executesql @sql;

SELECT * FROM #RADHE

在此處輸入圖像描述

SELECT
   tt.name AS table_type_name,
   c.name AS column_name,
   c.column_id,
   t.name AS type_name,
   c.max_length,
   c.precision,
   c.scale,
   c.collation_name,
   c.is_nullable
FROM 
   sys.columns As c
   JOIN sys.table_types AS tt
       ON c.object_id = tt.type_table_object_id
   JOIN sys.types AS t
       ON t.user_type_id = c.user_type_id
ORDER BY
   tt.name,
   c.column_id

在此處輸入圖像描述

我什至可以使用以下腳本對所有使用者定義的類型進行 GRANT REFERENCE ：

   SELECT t.name, 
          'GRANT REFERENCES ON TYPE::' 
          + SCHEMA_NAME(t.schema_id) 
          + '.' 
          + t.name 
          + ' TO public;' AS command_to_run
   FROM   sys.types AS t
where 1=1
AND T.is_table_type = 1

在此處輸入圖像描述

但是有沒有辦法編寫出數據庫中所有表類型的腳本？

我正在尋找編寫此表類型的腳本，請注意使用它創建的約束和索引：

use TableBackups
go

IF EXISTS(SELECT * 
           FROM SYS.table_types tt
          WHERE tt.NAME=N'DistCritGroupData' 
            AND SCHEMA_NAME(tt.SCHEMA_ID) = N'dbo')
  DROP TYPE DBO.DistCritGroupData

CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
 (
   [DistCritTypeId] [int] NOT NULL UNIQUE,
   [ItemAction] [int] NOT NULL,        
   [ObjectId] [int] NOT NULL,
   [OperatorType] [int] NOT NULL,

   PRIMARY KEY NONCLUSTERED 
   (
      [DistCritTypeId] ASC
   ),

   INDEX CIX CLUSTERED (ObjectId, OperatorType)    
 );

這是我創建使用者定義表和標量類型的腳本：

-- http://www.sqlines.com/sql-server-to-oracle/create_type


SELECT 
     sch.name AS UDT_SCHEMA_NAME 
    ,userDefinedTypes.name AS UDT_TYPE_NAME 

   ,
   N'IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''' + REPLACE(userDefinedTypes.name, '''', '''''') + N''' AND ss.name = N''' + REPLACE(sch.name, '''', '''''') + N''') '
   + NCHAR(13) + NCHAR(10) 
   +
   CASE WHEN userDefinedTypeProperties.IsTableType = 1 
       THEN N'CREATE TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name) + ' AS TABLE ( 
   ' + tAllColumns.column_definition  + N'
); ' 
       ELSE 
           + N'CREATE TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name) 
           + N' FROM ' 
           + tBaseTypeComputation.baseTypeName 
           + CASE WHEN userDefinedTypeProperties.is_nullable = 0 THEN N' NOT NULL' ELSE N'' END 
           + N'; ' 
   END AS SqlCreateUdt

FROM sys.types AS userDefinedTypes 

INNER JOIN sys.schemas AS sch 
   ON sch.schema_id = userDefinedTypes.schema_id 

LEFT JOIN sys.table_types AS userDefinedTableTypes 
   ON userDefinedTableTypes.user_type_id = userDefinedTypes.user_type_id 

LEFT JOIN sys.types AS systemType 
   ON systemType.system_type_id = userDefinedTypes.system_type_id 
   AND systemType.is_user_defined = 0 

OUTER APPLY 
   (
       SELECT 
            userDefinedTypes.is_nullable 
           ,userDefinedTypes.precision AS NUMERIC_PRECISION 
           ,userDefinedTypes.scale AS NUMERIC_SCALE 
           ,userDefinedTypes.max_length AS CHARACTER_MAXIMUM_LENGTH
           ,CASE WHEN userDefinedTableTypes.user_type_id IS NULL THEN 0 ELSE 1 END AS IsTableType 
           ,CONVERT(smallint, 
                   CASE -- datetime/smalldatetime  
                   WHEN userDefinedTypes.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(userDefinedTypes.system_type_id, userDefinedTypes.scale) 
                   END
           ) AS DATETIME_PRECISION 
   ) AS userDefinedTypeProperties 


OUTER APPLY 
   (
       SELECT 
           systemType.name 
           + 
           CASE 
               WHEN systemType.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') 
                   THEN N'(' 
                       + 
                       CASE WHEN userDefinedTypeProperties.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
                           ELSE CONVERT
                               (
                                   varchar(4)
                                   ,userDefinedTypeProperties.CHARACTER_MAXIMUM_LENGTH 
                               )
                       END 
                       + N')' 
               WHEN systemType.name IN ('decimal', 'numeric')
                   THEN N'(' + CONVERT(varchar(4), userDefinedTypeProperties.NUMERIC_PRECISION) + N', ' + CONVERT(varchar(4), userDefinedTypeProperties.NUMERIC_SCALE) + N')'
               WHEN systemType.name IN ('time', 'datetime2', 'datetimeoffset') 
                   THEN N'(' + CAST(userDefinedTypeProperties.DATETIME_PRECISION AS national character varying(36)) + N')' 
               ELSE N'' 
           END AS baseTypeName  
   ) AS tBaseTypeComputation 

OUTER APPLY 
   (
       SELECT 
           (
               SELECT 
                   -- ,clmns.is_nullable 
                   -- ,tComputedProperties.ORDINAL_POSITION
                   -- ,tComputedProperties.COLUMN_DEFAULT

                     CASE WHEN tComputedProperties.ORDINAL_POSITION = 1 THEN N' ' ELSE N',' END 
                   + QUOTENAME(clmns.name) 
                   + N' '
                   + tComputedProperties.DATA_TYPE 
                   + 
                   CASE 
                       WHEN tComputedProperties.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') 
                           THEN N'(' 
                               + 
                               CASE WHEN tComputedProperties.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
                                   ELSE CONVERT
                                       (
                                           varchar(4)
                                           ,tComputedProperties.CHARACTER_MAXIMUM_LENGTH 
                                       )
                               END 
                               + N')' 
                       WHEN tComputedProperties.DATA_TYPE IN ('decimal', 'numeric')
                           THEN N'(' + CONVERT(varchar(4), tComputedProperties.NUMERIC_PRECISION) + N', ' + CONVERT(varchar(4), tComputedProperties.NUMERIC_SCALE) + N')'
                       WHEN tComputedProperties.DATA_TYPE IN ('time', 'datetime2', 'datetimeoffset') 
                           THEN N'(' + CAST(tComputedProperties.DATETIME_PRECISION AS national character varying(36)) + N')' 
                       ELSE N'' 
                   END 
                   + CASE WHEN tComputedProperties.is_nullable = 0 THEN N' NOT NULL' ELSE N'' END 
                   + NCHAR(13) + NCHAR(10) 
                   AS [text()]
               FROM sys.columns AS clmns 
               INNER JOIN sys.types AS t ON t.system_type_id = clmns.system_type_id 
               LEFT JOIN sys.types ut ON ut.user_type_id = clmns.user_type_id 

               OUTER APPLY 
                   (
                       SELECT 
                            33 As bb 
                           ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'ordinal')  AS ORDINAL_POSITION 
                           ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH 
                           ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH 
                           ,CONVERT(nvarchar(4000), OBJECT_DEFINITION(clmns.default_object_id)) AS COLUMN_DEFAULT 

                           ,clmns.is_nullable 
                           ,t.name AS DATA_TYPE

                           ,CONVERT(tinyint, 
                               CASE -- int/decimal/numeric/real/float/money  
                                   WHEN clmns.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN clmns.precision  
                               END
                            ) AS NUMERIC_PRECISION 

                           ,CONVERT(int, 
                               CASE -- datetime/smalldatetime  
                                   WHEN clmns.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL  
                                   ELSE ODBCSCALE(clmns.system_type_id, clmns.scale) 
                               END
                            ) AS NUMERIC_SCALE

                           ,CONVERT(smallint, 
                                CASE -- datetime/smalldatetime  
                                   WHEN clmns.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(clmns.system_type_id, clmns.scale) 
                                END
                           ) AS DATETIME_PRECISION 
                   ) AS tComputedProperties  

               WHERE clmns.object_id = userDefinedTableTypes.type_table_object_id 
               ORDER BY tComputedProperties.ORDINAL_POSITION 

               FOR XML PATH(''), TYPE 
           ).value('.', 'nvarchar(MAX)') AS column_definition 
   ) AS tAllColumns  

WHERE userDefinedTypes.is_user_defined = 1

引用自：https://dba.stackexchange.com/questions/245468

相關問答

Sql-Server

日誌傳送中已經設置了哪些數據庫，哪些沒有？

  • May 17, 2021
檢視問答
Sql-Server

如何在 sql server 中編寫伺服器級對象的腳本？

  • March 20, 2021
檢視問答
Sql-Server

如何格式化 OBJECT_DEFINITION 的輸出，使其看起來與 sp_helptext 相同？

  • March 10, 2021
檢視問答
Sql-Server

SQL Server 命令行安裝 - 如何更改版本？

  • November 2, 2020
檢視問答
Sql-Server

如何在 SSAS 伺服器上編寫管理員權限腳本？

  • October 7, 2020
檢視問答
Sql-Server

顯示表的所有權限的腳本

  • June 2, 2020
檢視問答