Can you drop a table that’s used in a stored procedure?

Today’s post comes directly from a conversation with a close friend of mine who is a developer. Since developers deploy changes often, they need to know what order to execute their changes in.

The answer is yes, but there’s a catch

By default, normal stored procedures don’t prevent you from dropping a table. Let’s look at a demo.

SELECT *
INTO TestTable
FROM sys.messages
GO

INSERT INTO TestTable
SELECT * FROM sys.messages
GO 10

That should give us plenty of junk data. Now we need a procedure, so let’s SELECT * from that new table.

CREATE PROCEDURE [Query_TestTable] as 
BEGIN
SELECT * FROM TestTable
END
GO

Now we just need to drop the table.


Once we drop the table, the stored procedure returns an error.


But I said there’s a catch. The catch is if the stored procedure is currently running, you can’t drop the procedure.

First, re-run the script to create the table and insert some rows.

Second, start running the procedure. Then run the drop table command and watch it wait. I’ll use sp_WhoIsActive to take a look at the current running processes.

LCK_M_X (sound familiar?)

It looks a lot like LCK_M_SCH_S, which I previously blogged about here.

In this case, the X stands for exclusive. Before we can drop the table, the stored procedure has to finish using the table.

Thanks for reading! Stay tuned.


By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close