Delete tables using SQL query based on table name

Question

I have 1000 tables in my SQL Server database. Table names like CIP_01_12_2022_10_15_20 that I have 1000 table. Table name saved as data time(CIP_DD_MM_YYYY_HH_MM_SS).

So I need to delete like between particular dates.

Tables named like below

CIP_01_12_2022_10_15_20
CIP_01_12_2022_10_15_25
CIP_01_12_2022_10_15_35
CIP_01_12_2022_10_15_45
CIP_01_12_2022_10_15_55
CIP_01_12_2022_10_15_58
CIP_01_12_2022_10_15_59
CIP_01_12_2022_10_16_20
CIP_01_12_2022_10_16_25
CIP_02_12_2022_10_15_20

In the above, I have to delete between two dates. For example I have to delete between these dates 01-12-2022 00:00:00 to 01-12-2022 11:59:59 delete all tables except 2nd December 2022 table.

Answer 1

I think you can use a cursor in order to do that:

DECLARE table_cursor CURSOR FOR
SELECT name
FROM sys.tables
WHERE name LIKE 'CIP_%'

Then do loop on it.

EXEC('DROP TABLE ' + @<yourFetchVariable>)