Which user defined data type is memory optimized?

Posted on

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.

Leave a Reply

Your email address will not be published. Required fields are marked *