Sql-Server
如何編寫使用者定義的表類型?
我可以 使用以下任一腳本獲取所有表類型的名稱和定義:
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