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.

Table names can't be parameters. You'll have to construct and execute a SQL command for every table. That's a bad design to begin with. SQL Server allows transparent table partitioning in all supported versions and editions - even Express. You can create time-based partitions on the `CIP` table that are invisible to applications and just remove the partitions you don't want. Or issue normal DELETE commands — Panagiotis Kanavos, Jan 31 '23 at 11:31
You could create an updateable view with `UNION ALL` and use `DELETE` with it. I'm not sure if you can use 1000 tables though. You'll also have to ensure each table has `CHECK` constraints that limit the possible date values, otherwise the server will have to scan all tables for matches. On the other hand, you already try to do that by hand — Panagiotis Kanavos, Jan 31 '23 at 11:34
Unless each table is very large, partitioning may well be overkill. Simply inserting rows with the appropriate timestamp in *one* destination table and using a regular old `DELETE` likely suffices. The ingesting process may need to become a little bit more complicated for that, but that's well worth the effort as opposed to sorting this out in T-SQL after the fact. — Jeroen Mostert, Jan 31 '23 at 11:35
How many rows are there? Why are 1000 tables used at all ? Unless there are 1Bn (Billion) rows, there's no need for 1000 partitions, much less 1000 tables. Definitely not a table per second — Panagiotis Kanavos, Jan 31 '23 at 11:36
@PanagiotisKanavos unfortunately wrong data created with huge tables.. every table we have 50 rows. — Cric News For Fans, Jan 31 '23 at 11:48
Then you need to fix the bug, not cover it up. Executing 1000 DELETEs in a loop will be very expensive, even if the tables are empty. 50K rows is no data at all. That's so little data it can easily fit into the CPU's cache — Panagiotis Kanavos, Jan 31 '23 at 11:52
Is this a "one-off fix" you need to do or a process you need to put in place for a rolling-delete over time — Stu, Jan 31 '23 at 11:54
bug fixed. unfortunately.. i said 1000 table only .. in reality.. there is 10k tables created due to wrong code.. no the thing is have to delete 10k tables :( — Cric News For Fans, Jan 31 '23 at 11:57
If this is a one-off, you could create the `DELETE` or `DROP` statements in C#, in a single SQL script. I suspect the original problem was caused by such scripts though. — Panagiotis Kanavos, Jan 31 '23 at 11:58
If your database contains only these tables, it's much faster to insert the data into a new one (using a single table this time) and drop the old database altogether. Even if not, if downtime allows, it may well be faster to migrate the existing tables (or restore a backup) rather than delete these ones, since it'll be a slow process regardless. Of course if you need 24/7 uptime or your existing tables are large it's another matter. — Jeroen Mostert, Jan 31 '23 at 12:09

score 0 · Accepted Answer · answered Jan 31 '23 at 11:35

0

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

answered Jan 31 '23 at 11:35

Christophe Van Beneden

24
3

in c# i just take table name and split the table and i can find dates and can easly delete through c#. But i need using query even i don't know how to write loop in sql – Cric News For Fans Jan 31 '23 at 11:52

score 0 · Answer 2 · answered Jan 31 '23 at 12:20

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

Delete tables using SQL query based on table name

2 Answers2