2

How can i know which one of my uddts is memory optimized? and how can i know it's definition?

3

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.

  • The first query misses a filter so it returns all the table types – sepupic Jan 29 '18 at 10:38
  • @sepupic, thanks for the catch. I added the filter. – Dan Guzman Jan 29 '18 at 11:26
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.