2

Good morning,

My first question is how to determine if tables created in MS SQL Server are memory optimized. I have some tables and I don't remember if some of them I created in-memory optimized or not.

Many thanks for answers.

Piotr Wojcik
  • 23
  • 3

2 Answers2

6

To riff off of another answer here, here's a way to get the status for all tables in your database:

select name, OBJECTPROPERTY(object_id,'TableIsMemoryOptimized')
from sys.tables;

Similarly, if you want just the in-memory ones, you could do:

select name
from sys.tables
where OBJECTPROPERTY(object_id,'TableIsMemoryOptimized') = 1;
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
2

Select OBJECTPROPERTY(OBJECT_ID('schema.tablename'),'TableIsMemoryOptimized')

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
Daniel N
  • 1,122
  • 1
  • 8
  • 14
  • Unfortunately I faced error message: "Invalid column name 'TableIsMemoryOptimized'." I am using MS SQL Server 2019 Developer. – Piotr Wojcik Nov 15 '19 at 14:46
  • I've submitted an edit to the commenter's answer that should make it work for you, @PiotrWojcik. Have a look. – Ben Thul Nov 15 '19 at 16:01
  • How to determine if given synonym contains memory optimized table? – ZedZip Jan 24 '23 at 12:15
  • @ZedZip - use the `sys.synonyms` view to find what table the synonym points to and then use the above method to determine if said table is memory-optimized. General advice here though - if you have a separate but related question, it's better to start a new question than to reply to one that's over three years old. You have a better chance of having it answered that way! – Ben Thul Jan 24 '23 at 14:54