Drop tables in a database which are created older than two months from current date

Question

I need to drop multiple tables in my DB which got created more than two months from current time.

My database has these sample tables:

• Table_A_20200101
• Table_B_20200212
• Table_C_20200305
• Table_Exp
• Table_XYZ

Answer 1

Seems odd to be relying on a string in the table's name than the metadata to determine when a table was created, but ok:

CREATE TABLE #TablesToDrop
(
  s sysname, n sysname, d AS (CONVERT(date, RIGHT(n,8)))
);

INSERT #TablesToDrop(s, n) 
  SELECT s.name, t.name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  WHERE ISDATE(RIGHT(t.name,8)) = 1;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  DROP TABLE ' + QUOTENAME(s) + N'.' + QUOTENAME(n) + N';'
FROM #TablesToDrop
WHERE d < DATEADD(MONTH, -2, GETDATE());

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that this won't generate the DROP list in any predictable or dependency order, won't handle foreign keys or other dependencies that prevent a table from being dropped, etc.

Answer 2

You can try below:

Declare @DropQuery varchar(max)
    
SELECT 
    @DropQuery = ISNULL(@DropQuery +' ', '') + 'drop table ' + T.tableName
FROM 
( 
    select name as tableName, create_date  from sys.tables
    where create_date < dateadd(m,-2,getdate())
    
    
)T
order by  create_date desc
    

Print @DropQuery
EXEC(@DropQuery) 

Added create_date order because to drop first reference table. Note: reference should be added while creating table then query will work proper.