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;