From time to time we have the need to drop multiple tables within a single stored procedure. Were you aware that it can be done in single DROP TABLE statement? SELECTing multiple columns, constants, variables, etc. in a single SELECT is one of the first things we learn as SQL newbies. But dropping two or more tables at the same time, while slightly less common, is just as convenient.
So, instead of…
DROP TABLE A; DROP TABLE B; DROP TABLE C;
DROP TABLE A, B, C;
Not only can we do this with normal tables, we also have the ability to drop temp tables; both local and global, as well at the same time. How convenient is that?
This little block of code will create normal, local temp, & global temp tables, fill them in with sample data, show the data’s there, drop the tables using this “new” syntax, then show that all three tables are indeed gone.
USE tempdb SET NOCOUNT ON -- Create a local temp table CREATE TABLE #localTempTable (i INT) -- Fill the local temp table with sample data INSERT INTO #localTempTable SELECT id FROM sysobjects -- Show that the local temp table is filled SELECT COUNT(i) localTempTable FROM #localTempTable GO -- Create a global temp table CREATE TABLE ##globalTempTable (i INT) -- Fill the global temp table with sample data INSERT INTO ##globalTempTable SELECT id FROM sysobjects -- Show that the global temp table is filled SELECT COUNT(i) globalTempTable FROM ##globalTempTable GO -- Create a real table CREATE TABLE RealTable (i INT) -- Fill the real table with sample data INSERT INTO RealTable SELECT id FROM sysobjects -- Show that the real table is filled SELECT COUNT(i) realTable FROM RealTable GO -- Drop the tables using the "new" syntax DROP TABLE #localTempTable, ##globalTempTable, RealTable GO -- Show that the objects are indeed gone IF OBJECT_ID(N'tempdb..#localTempTable', N'U') IS NULL SELECT '#localTempTable is gone' ELSE SELECT '#localTempTable is still there' IF OBJECT_ID(N'tempdb..##globalTempTable', N'U') IS NULL SELECT '##globalTempTable is gone' ELSE SELECT '##globalTempTable is still there' IF OBJECT_ID(N'tempdb..RealTable', N'U') IS NULL SELECT 'RealTable is gone' ELSE SELECT 'RealTable is still there'
And while these days it seems like we DELETE less and archive a lot more, it’s still nice to know that we do have a couple options when it comes to performing this most basic, but vital operation in SQL Server.
Have a grateful day…Troy