-2

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
harun raseed
  • 21
  • 2
  • 3
    What have you tried? Where did you get stuck? – Dale K Aug 18 '20 at 03:35
  • SELECT SCHEMA_NAME(T.schema_id) + '.' + T.name TableName, REPLACE((RIGHT(T.name, 8) ), '_', '-') TableDate --CONVERT(DATE, CAST(CAST((RIGHT(T.name, 8)) AS varchar) AS CHAR(8)), 112) as TableDate INTO #M FROM sys.tables T --WHERE ISNUM(TableDate) = 1; select * from #M I have tried this, but not working as per the requirement – harun raseed Aug 18 '20 at 03:38
  • 2
    [edit] it into your question – Dale K Aug 18 '20 at 03:38
  • 1
    Hang on that is a SELECT query. Dropping tables does not use a select query. A DROP TABLE only requires the table name. So what are you actually trying to do here? Do you need to determine which ones to drop? Or do you know and just not know how to drop them? – The Grand J Aug 18 '20 at 03:43
  • I am planning to select the required tables and insert it into an temp table and then drop it. I need to select only tables which are ending with date value (yyyymmdd) and then drop it – harun raseed Aug 18 '20 at 03:46
  • So you want to rely on parsing the right-most 8 characters in a table's name to determine when it was created? Why wouldn't you use `sys.tables.create_date`? – Aaron Bertrand Aug 18 '20 at 04:02
  • I suspect that your data modeling is messed up and you should really be using partitioned tables. – Gordon Linoff Aug 18 '20 at 13:36

2 Answers2

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

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.

Jignesh Bhayani
  • 83
  • 3