CODEWITHSUNDEEP
sql-serversql-server-2016sql-server-2019
Piotr Wojcik
Piotr Wojcik

Reputation: 33

How to determine if given table is memory optimized?

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.

Upvotes: 3

Views: 3558

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

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;

Upvotes: 7

Daniel N
Daniel N

Reputation: 1208

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

Upvotes: 1

Related Questions