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.

Which dbms? To solve this, you'll probably need product specific functionality. — jarlh, May 06 '15 at 10:06

GarethD · Accepted Answer · 2015-05-06T11:35:55.910

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;

Many thanks for this, you are correct, it is SQL. Unfortunatley I get errors when trying to run this script - Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('. Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'FOR'. Msg 137, Level 15, State 2, Line 9 Must declare the scalar variable "@SQL". — user3820135, May 06 '15 at 11:32
I have updated the answer, the syntax I used was for sql server 2008 (and later), I guess that you are using SQL Server 2005, which does not allow you to declare a variable and assign it in the same step. — GarethD, May 06 '15 at 11:36
Thats done it perfectly, thanks. Will setup a job to run this monthly. — user3820135, May 06 '15 at 13:43

score 0 · Answer 2 · answered May 06 '15 at 10:26

0

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

answered May 06 '15 at 10:26

techno

192
13

Many thanks for this. Looks like it works perfectly, but I do not know cursor very well so reading up about it now. Queick question, when running this with in a rollback I get 'Commands completed' message. Is there a way to list out the command it has ran? Thanks – user3820135 May 06 '15 at 11:28
For example, usually if I run an update I use a select statement in the rollback script to see the changes. I can't seem to do that with this. Thanks – user3820135 May 06 '15 at 11:30
I am sure this is a correct answer and I thank you very much for your time, unfortunatley I do not quite understand the workings of it at the moment so will research cursors further. – user3820135 May 06 '15 at 13:44
np.. :) I dint understand your question in the comment. – techno May 07 '15 at 16:58

Dropping tables automatically depending on modify date

2 Answers2