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 10That 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
GONow 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.
