How can I Delete data from Multiple tables with similar Table names?-CodePudding

Let's say I have 400 tables named Test_1, Test_2..., Test_400

And I would like to empty those tables (not Drop).

Basically I would like to do a:

DELETE FROM Schema
WHERE Table_Name LIKE 'Test%'

Is there a way to do this?

CodePudding user response：

Assuming no foreign keys, this should do the trick.

There is obviously some danger in a script like this - I suggest a "dry run" where you just print out the statements, then uncomment the line that actually runs the deletes once you are happy.

DECLARE c CURSOR FAST_FORWARD FOR 
SELECT 'DELETE FROM '   TABLE_SCHEMA   '.'   TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE 'TEST%'

DECLARE @sql NVARCHAR(MAX);
OPEN c;

FETCH NEXT FROM c INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @sql;
    --UNCOMMENT THE NEXT LINE ONCE YOU ARE HAPPY ITS NOT GOING TO DELETE SOMETHING IT SHOULDN'T
    --EXEC sys.sp_executesql @sql
    FETCH NEXT FROM c INTO @sql;
END

CLOSE c;
DEALLOCATE c;