Delete tables older than 12 months using table name

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.

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;

This query will populate a temporary table having only those where the end of the table name is a date:

SELECT SCHEMA_NAME(T.schema_id) + '.' + T.name TableName,
  REPLACE((RIGHT(T.name, 7) + '_01'), '_', '-') TableDate
INTO #M
FROM sys.tables T
WHERE ISDATE(REPLACE((RIGHT(T.name, 7) + '_01'), '_', '-')) = 1;

The next part is deleting any tables that are still "current" - meaning that the date extension is within your 12-month window:

DELETE FROM #M
WHERE DATEADD(MONTH, -12, TableDate) < GETDATE();

Now you're left with only the matching tables in #M, so you can just loop through in whichever fashion you'd like, executing dynamic SQL to drop the table(s):

WHILE (EXISTS (SELECT * FROM #M)) BEGIN
  DECLARE @TableName VarChar(100) = (SELECT TOP 1 TableName FROM #M);
  DECLARE @SQL NVarChar(1000) = 'DROP TABLE ' + @TableName;
  EXEC (@SQL);
  DELETE FROM #M WHERE TableName = @TableName;
END;

For cleanup, drop the temporary table as well:

DROP TABLE #M;

If the date is the last 7 then this should work to give you the list dates. I could not really test the last as none of my tables conform to that format. The problem this has is that if any of the table names do not conform to that format then the select fails. You will need to add the delete / drop syntax but hopefully this will get you a list.

    select name from sysobjects where xtype='u'

    select DATEDIFF(dd, CONVERT(datetime,REPLACE(SUBSTRING('TABLE_C_2010_08',LEN('TABLE_C_2010_08')-6, 7),'_','.') + '.01',101), GETDATE())


    select name 
    from sysobjects 
    where xtype='u'
    and DATEDIFF(dd, CONVERT(datetime,REPLACE(SUBSTRING(name,LEN(name)-6, 7),'_','.') + '.01',101), GETDATE()) > 0