Delete tables older than 12 months using table name

Question

I want to be able to drop tables that are older than 12 months. The tables have the date (month and year) in their name. For example TABLE_A_2011_01 has a date of January 2011.

What I want to do is drop those tables where the date part is older than 12 months. If today's date is September 15, 2011 I want to drop all tables that are older than September 15, 2010.

Answer 1

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH p(o,d) AS
(
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME(name),
        d = RIGHT(REPLACE(name, '_', ''), 6) + '01'
      FROM sys.tables 
      WHERE ISDATE(RIGHT(REPLACE(name, '_', ''), 6) + '01') = 1 
)
SELECT @sql += 'DROP TABLE ' + o + ';' FROM p
    WHERE d < CONVERT(CHAR(8), DATEADD(MONTH, -12, CURRENT_TIMESTAMP), 112);

PRINT @sql;
--EXEC sp_executesql @sql;