-1

Table

Please, can someone suggest how I can drop the tables that are in a SQL table as values? As per the example, I would like to drop them if they are expired.

https://i.stack.imgur.com/POhfV.png

Thanks in advance!

smad
  • 13
  • 4
  • 2
    I wont give you a full answer as you did not appear to have tried anything. I will give a hint though, I have done this before using a query with the stuff function, or maybe it was 'for xml path', or maybe both. It was a long time ago. You could also look up tsql cursors. – Joe C Jun 15 '17 at 16:48

2 Answers2

1

Here is another way to do this, if you don't want to use a cursor:

DECLARE @Run INT,
        @SQL VARCHAR(500)

CREATE TABLE #TableList
(
  TableID INT IDENTITY(1,1),
  TableCMD VARCHAR(500)
);

INSERT INTO #TableList (TableCMD)
SELECT 'DROP TABLE ' + tablename
FROM   YourTable
WHERE  ExpiryDate < GETDATE();

SET @Run = (SELECT  MAX(TableID)
            FROM    #TableList)
WHILE @Run > 0
BEGIN
    SELECT @SQL = ( SELECT  TableCMD 
                    FROM    #TableList
                    WHERE   TableID = @Run)
    EXEC (@SQL)
    SET @Run = @Run - 1
END

You should probably also delete the records of expired tables from your main table, so you don't get failures next time when you attempt to drop tables that don't exist. Or better yet, add a status column to mark them as deleted, but keep the records for reference.

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • 1
    I prefer this `non-cursor` way to loop through table too. – pkuderov Jun 15 '17 at 17:04
  • Thanks Aaron, That is working like a charm. After I tried so many things this worked perfectly! – smad Jun 16 '17 at 08:15
  • @smad Happy to help, please accept the answer :) Side note, your question got a couple downvotes and that was mainly because you linked to images instead of typing your code. Images are frowned upon because they can expire, and future visitors that have a similar question won't be able to see your code. Welcome to SO, hope to see you around. – Aaron Dietz Jun 16 '17 at 20:05
  • This is something I will probably never understand and it drives me mad! I keep looking for a "no loop" way to solve stuff like this, (because my boss usually can and every lecture says "dont loop") then I end up looping or processing tables in languages that one can understand easily. (At that point I am almost ready to hurt anyone who likes SQL and crosses my way.) All that time and effort is a waste because the language was designed without a performant way of looping, which is - as seen here - NEEDED! >:( (This comment will get helpful with further links to this topic by later comments:P) – Jan May 11 '18 at 14:25
0

Like this with a cursor and dynamic queries: Drop all tables whose names begin with a certain string

DECLARE @tname varchar(100)
DECLARE table_cursor CURSOR FOR
    SELECT TableName FROM TableCatalog WHERE GETDATE() > ExpiryDate 

OPEN cursor 
FETCH NEXT FROM table_cursor INTO @tname 
WHILE @@fetch_status = 0 
BEGIN
    EXECUTE sp_executesql 'DROP TABLE ' + @tname 
    FETCH NEXT FROM table_cursor INTO @tname 
END
CLOSE table_cursor;
DEALLOCATE table_cursor
rlb.usa
  • 14,942
  • 16
  • 80
  • 128