:::: MENU ::::

Drop specific tables through a stored procedure by using Cursor

  • Dec 20 / 2012
  • 0
Cursors, dbDigger, T-SQL Scripts, User Defined Stored Procedures

Drop specific tables through a stored procedure by using Cursor

Suppose we have to create a procedure which would drop all the tables having a given naming convention. Procedure would utilize a cursor and dynamic SQL. Table names would be pulled from SYS.Objects catalog. Here is the commented procedure for demo

/*   
 Demo would drop the required tables  
 by using a cursor in following steps  
 1. Create tables for drop demo  
 2. Verify the created tables  
 3. Create USP  
 4. Create cursor in USP to pull and execute the drop commands  
 5. Execute the procedure  
 6. Verify that tables do not exist now  
 */  
 -- Create Tables  
 create table rep1 (id smallint)  
 GO  
 create table rep2 (id smallint)  
 GO  
 create table rep3 (id smallint)  
 GO  
 create table rep4 (id smallint)  
 GO  
 -- verify the created tables   
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

 -------------------------------------------------------------  
 -- Create USP and a cursor in USP  
 If exists (select name from sys.objects   
 where type = 'P' and name = 'USP_DropRepTables')  
 DROP PROCEDURE USP_DropRepTables  
 GO  
 CREATE PROCEDURE USP_DropRepTables  
 AS  
 -- Declare variable for SQL command  
 DECLARE @dropQuery NVARCHAR(400)  
 -- Declare and populate cursor with required tables  
 DECLARE DropLoopCursor CURSOR FOR  
 SELECT 'DROP Table '+name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 -- Open cursor  
 OPEN DropLoopCursor  
 -- Fetch record from cursor  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 -- Configure while loop in cursor  
 WHILE (@@FETCH_STATUS <> -1)  
 BEGIN  
 -- Execute Dynamical SQL  
 execute (@dropQuery)  
 -- Fetch next recod  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 END  
 -- Close and deallocate the cursor  
 CLOSE DropLoopCursor  
 DEALLOCATE DropLoopCursor  
 ------------------------------------------------------------  
 -- End the procedure  
 GO  

 -- Execute the procedure  
 EXECUTE USP_DropRepTables  
 GO  

 -- Verify that tables do not exist now  
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

You may copy and paste the whole code in SSMS query pane for a better view of comments. Also it is required to take care that given naming convention should not be used by any such table that is not required to drop.

Consult us to explore the Databases. Contact us