Question :
How can i know which one of my uddts is memory optimized? and how can i know it’s definition?
Answer :
The query below will identify memory-optimized table types in the current database.
SELECT
OBJECT_SCHEMA_NAME(tt.type_table_object_id) AS SchemaName
, tt.name TableTypeName
FROM sys.table_types AS tt
JOIN sys.objects AS o ON
o.object_id = tt.type_table_object_id
WHERE tt.is_memory_optimized = 1;
You can query other catalog views to get other meta-data. For example, columns
can be listed with this query:
SELECT
OBJECT_SCHEMA_NAME(tt.type_table_object_id) AS SchemaName
, tt.name AS TableTypeName
, c.name AS ColumnName
, ty.name AS ColumnTypeName
, c.max_length AS ColumnLength
, c.is_nullable AS IsNullable
FROM sys.table_types AS tt
JOIN sys.objects AS o ON
o.object_id = tt.type_table_object_id
JOIN sys.columns AS c ON c.object_id = o.object_id
JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
WHERE tt.is_memory_optimized = 1
ORDER BY
SchemaName
, TableTypeName
, c.column_id;
If you need the complete definition with indexes, constraints, etc. as DDL, script the type using SSMS Object Explorer or PowerShell.
If by “uddts” you mean memory-optimized table tables, then the following query will work:
SELECT tt.name AS [Name]
FROM sys.table_types AS tt
WHERE tt.is_memory_optimized = 1
ORDER BY tt.name;
For the definition, you could use sp_helptext.