Delete tables using SQL query based on table name-CodePudding

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.

CodePudding user response：

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>)

CodePudding user response：

It sounds like this is a one-time fix - the simplest and quickest way would be to just query the table names you need to drop and run the drop statements using SSMS.

Use the following as an example for how you can do this. If you really have 10k tables you might want to do in batches.

declare @sql nvarchar(max);

select @sql = String_Agg(Convert(nvarchar(max),Concat('drop table ', QuoteName([name]))), ';')
from sys.tables
where [name] like 'CIP%' and <other fitering criteria>

select @sql;

exec (@sql);

CodePudding user response：

Below is an example that uses a strict pattern matching for the dated tables and extracts the date value from the name for use in the date range criteria (a single date per your sample data).

DECLARE 
      @StartDate date = '20221201' --ISO 8601 date format to avoid ambiguity
    , @EndDate date = '20221201'
    , @DropScript nvarchar(MAX);
WITH date_tables AS (
    SELECT name AS TableName, TRY_CAST(SUBSTRING(name, 11, 4) SUBSTRING(name, 8, 2) SUBSTRING(name, 5, 2) AS date) AS TableDate
    FROM sys.tables
    WHERE name LIKE N'CIP[_][0-9][0-9][_][0-9][0-9][_][0-9][0-9][0-9][0-9][_][0-9][0-9][_][0-9][0-9][_][0-9][0-9]'
)
SELECT @DropScript = STRING_AGG(N'DROP TABLE '   TableName, N';')   N';'
FROM date_tables
WHERE TableDate BETWEEN @StartDate AND @EndDate;
EXEC sp_executesql @DropScript;
GO