Is there an easy way to delete all records in multiple tables (but not every table)?

Question

So I "fixed" an issue the other day, which has caused a snowball of new issues (imagine that). I have a large group of tables that serve as Temp tables for many import/export processes that flow to other systems. Each table is labeled "TO_xxxx". I want to clear all records from each "TO_" table. Is there a way to structure a delete statement to clear all records from only "TO_" tables?

Is it as easy as

Delete from TO_%

or is there more to it? There's about 80 "TO_" tables total, so I figured I'd post here before manually going through everything. If you can help, I would appreciate it. Thanks.

System is SQL Server 2016.

Answer 1

Borrowing from this answer, which drops tables with a common prefix, you could use something like this

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DELETE FROM ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'to[_]%';

PRINT @sql;
-- EXEC sp_executesql @sql;

---

The above example prints the delete commands. When you are ready to actually run it, uncomment the EXEC