Script to get definitions of table types and Sequences

Question

I need to get the definition of some objects in the SQL server. I got the definitions of stored procedures, functions, views and triggers from sys.sql_modules.

But, I don't know how to get that of user-defined table types and of sequences. I am trying to create this in another database and automate this process.

Does anyone know how to get the definition of table types and sequences?

• David Browne - Microsoft: SMO will do this for you. learn.microsoft.com/en-us/dotnet/api/…

Answer 1

Still working on the create script, huh? ;-). Below the meta data of the objects requested.

/*The configuration of the sequences*/
SELECT *
FROM sys.objects AS o
 JOIN sys.sequences AS s
 ON s.object_id = o.object_id
WHERE o.type = 'SO';
/*The columns of the table types*/
SELECT tt.name,
 c.*
FROM sys.table_types tt
 INNER JOIN sys.columns c
 ON c.object_id = tt.type_table_object_id
ORDER BY c.column_id;

• Viz Krishna: Hi Peter , This only give the names of the types and sequences , what I needed was the definition . Thanks anyway . You guys are great 🙂
• Peter: The create script, is as far as I know not defined in a definition, but you can create it based on the columns in the s.* (sequences) and the c.* (table types).
• Viz Krishna: Yeah! I have don it for table types.now working on sequences . Thank you🙂❤️

Answer 2

I have a script that scripts out sequences, it does the creation, hope it helps

/*==============================================================*\
 script out the sequences 
 marcello miorelli
 v20231228
\*==============================================================*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT serverName = @@servername
 ,database_name = db_name()
 ,sequence_name= SCHEMA_NAME(s.schema_id) + '.' + s.name
 ,create_sequence=
'CREATE SEQUENCE [' + SCHEMA_NAME(s.schema_id) + '].[' + s.name + ']' + CHAR(13) +
' AS ' + TYPE_NAME(s.user_type_id) + CHAR(13) +
' START WITH ' + CAST(s.start_value AS VARCHAR) + CHAR(13) +
' INCREMENT BY ' + CAST(s.increment AS VARCHAR) + CHAR(13) +
' MINVALUE ' + CAST(s.minimum_value AS VARCHAR) + CHAR(13) +
' MAXVALUE ' + CAST(s.maximum_value AS VARCHAR) + CHAR(13) +
CASE WHEN s.is_cycling = 1 THEN ' CYCLE' ELSE ' NO CYCLE' END + CHAR(13) +
' ' + CASE WHEN s.is_cached = 1 THEN 'CACHE ' + CAST(s.cache_size AS VARCHAR) ELSE 'NO CACHE' END + ';' + CHAR(13) + CHAR(13)
FROM sys.sequences s WITH(NOLOCK)
ORDER BY SCHEMA_NAME(s.schema_id), s.name
OPTION(KEEP PLAN)

and I also have a script to script out the table types:

 SELECT 
 [DB]=DB_NAME()
 ,UDT_NAME = sch.name + '.' + userDefinedTypes.name
 --,sch.name AS UDT_SCHEMA_NAME 
 --,userDefinedTypes.name AS UDT_TYPE_NAME 
 ,
 CREATE_UDT=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
 ,DROP_UDT=N'IF 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'DROP TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name) 
 ELSE N'' END
 + N'; ' 
 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