How to drop tables programmatically

Question

I want to drop some tables programmatically that may or may not exist. The goal would be a scheduled job. I can make it a manual process by putting it in a SP but I believe in automation when possible. I found this How to drop tables based on sys.objects? but I cant figure out how to make it work. It only deleted one table.

I am using SQL Server 2005.

This is what I tried based on the above mentioned link:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'DROP TABLE '
  + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE create_date < dateadd(d, -7, getdate()) and [name] like 'tmpTableDump%';

EXEC sp_executesql @sql;

I have never made a scheduled job but I figured I should be able to do it once I get the code right. I am not a DBA but an application programmer.

WBratz provided the solution. Here is the completed answer:

DECLARE @sql NVARCHAR(MAX)
DECLARE @count int

SELECT @count = COUNT(*) from sys.tables where create_date < dateadd(d, -7, getdate()) and [name] like 'tmpTableDump%'
WHILE @COUNT > 0 
    BEGIN
    SELECT @sql = N'DROP TABLE '
         + QUOTENAME(SCHEMA_NAME([schema_id]))
         + '.' + QUOTENAME(name) + ';'
    FROM sys.tables
    WHERE create_date < dateadd(d, -7, getdate()) and [name] like 'tmpTableDump%'

    EXEC sp_executesql @sql;

    SET @count = (SELECT (@count - 1))
END

Czech out http://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists-in-sql-server — B. Clay Shannon-B. Crow Raven, Dec 17 '15 at 19:56

score 2 · Accepted Answer · answered Dec 17 '15 at 20:19

Its because only one value can be put into @sql at a time, so you'd need to loop this, so like select COUNT(*) from sys.tables where create_date < dateadd(d, -7, getdate()) and [name] like '%whatever%'

Then loop for however many the count is subtracting one every time so something like

 DECLARE @count int = select COUNT(*) from sys.tables where create_date < dateadd(d, -7, getdate()) and [name] like '%whatever%'
 DECLARE @sql NVARCHAR(MAX)

WHILE @COUNT > 0 
BEGIN
SELECT @sql = N'DROP TABLE '
 + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE create_date < dateadd(d, -7, getdate()) and [name] like '%whatever%'

EXEC sp_executesql @sql;

SET @count = (SELECT (@count - 1))
END

May not be exactly correct but thats why its only deleting one and you seem like a smart enough dude to see though my possible syntax errors to get it right

How to drop tables programmatically

1 Answers1