How can I drop a Memory Optimized Table?

Running SQL Server Standard 2016 SP2-CU1. I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them?

Running:

DROP TABLE [REF].[Work_xxyyzz]

Gives:


Msg 12332, Level 16, State 111, Line 1

Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

There are no user triggers or events on my In Memory Tables. 5 simple tables with primary keys.

There are no dependencies or references to these tables. They are like temp tables, we name them Work_xxxyyzz tables. However, they do belong to a database schema.

I am fine to keep the filegroup in place for now. I want to drop these tables in order to create default objects of the same name.

In addition to the restrictions pointed out in Evan's answer, there are a number of other things that can prevent dropping tables. Most of them are similar to the restrictions on normal disk-based tables, and you should see them called out in your error message.

For instance, if I have an in-memory table referenced by a natively-compiled (schemabound) stored procedure, I'll get this error if I drop the table:


Cannot DROP TABLE 'YourTable' because it is being referenced by object 'sp_YourStoredProc'.

The solution in that case is to drop the stored proc first, and then drop the table.

The error message you're getting indicates that there is a server or database level DDL trigger. You'll need to disable or drop those triggers in order to drop the in-memory table. You can find any such triggers by running these queries:

USE [master];
GO
SELECT * FROM sys.server_triggers;

USE [YourDatabaseName];
GO
SELECT * FROM sys.triggers;

You should look into what these triggers are doing (auditing, perhaps?). If it's safe to do so, consider disabling them with the DISABLE TRIGGER statement, then dropping your table, then using ENABLE TRIGGER to turn the triggers back on.

Note that server and database-scoped DDL triggers are different from object level DML triggers (which you mentioned that you checked for and there are none).

StackExchange Database Administrators Q#218223, answer score: 6