Dropping tables automatically depending on modify date

Question

Is there a way of dropping tables automatically weekly or monthly dependent on modify date? After some research on this site I found a script and modified it to the below.

select 
    'drop table Update_Backups.[' + s.name +'].[' + t.name +']' , t.modify_date
from 
    Update_Backups.sys.tables t
    inner join
    Update_Backups.sys.schemas s
    on
    s.schema_id = t.schema_id
where 
    modify_date < DATEADD(year,-01, GETDATE())

Unfortunately it still has a manual element as I have to copy the the results and run them. I would like this to be totally automatic but my skills don't reach that far.

Answer 1

I don't think there is any real advantage to this over a cursor, since the main cost is going to be executing the statement, not building it, but you can combine your statements using SQL Server's XML extensions, then use sp_executesql to execute them:

DECLARE @SQL NVARCHAR(MAX) = (SELECT 'DROP TABLE Update_Backups.' + 
                                        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + 
                                        QUOTENAME(OBJECT_NAME(t.object_id)) + ';
                                        '
                            FROM    sys.tables t
                            WHERE   t.modify_date < DATEADD(year,-01, GETDATE())
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

PRINT @SQL -- FOR DEBUGGING YOU CAN CHECK THE STATEMENT ABOUT TO BE EXECUTED
EXECUTE sp_executesql @SQL;

N.B. I have assumed you are using SQL Server based on the use of the catalog view sys.tables

---

EDIT

Updated syntax for SQL Server 2005:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = (SELECT 'DROP TABLE Update_Backups.' + 
                        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + 
                        QUOTENAME(OBJECT_NAME(t.object_id)) + ';
                        '
            FROM    sys.tables t
            WHERE   t.modify_date < DATEADD(year,-01, GETDATE())
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

PRINT @SQL -- FOR DEBUGGING YOU CAN CHECK THE STATEMENT ABOUT TO BE EXECUTED
EXECUTE sp_executesql @SQL;

Answer 2

You can try Cursor over the values

DECLARE @table VARCHAR(100),@sql VARCHAR(MAX)

DECLARE CURSOR_TB CURSOR FOR            
SELECT  DISTINCT t.name
FROM    Update_Backups.sys.tables t
JOIN    Update_Backups.sys.schemas s
ON      s.schema_id = t.schema_id
WHERE   modify_date < DATEADD(year,-01, GETDATE())

OPEN CURSOR_TB

    FETCH NEXT FROM CURSOR_TB INTO @table
    WHILE @@FETCH_STATUS=0
    BEGIN 

        IF OBJECT_ID(@table) is not null
        BEGIN
            SET @sql = 'DROP TABLE ' + @table
            EXEC (@sql)

        END
        FETCH NEXT FROM CURSOR_TB INTO @table
    END

    CLOSE CURSOR_TB
    DEALLOCATE CURSOR_TB