SQL query to delete all rows from tables where the table name contains a certain string

Question

I have a database with many tables, and the names of many of those tables contain the letters "PMO". I'd like to be able to delete all the rows from all of the tables whose names contain the string "PMO". So, delete from table "PMOThing" and "PMOOtherThing" but not from table "Thing" or "OtherThing", for example.

EDIT

It's been suggested that there's a question that is a duplicate. It's close, but not quite. The question is how to drop tables whose names contain a certain string. I don't want to drop these tables; I want to delete all of the rows from them, but keep the tables. If someone knows how to modify the code below (from the suggested duplicate question) to do what I want, that'd be cool!

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

Sounds similar to this [StackOverflow question](http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string) — John Odom, Apr 20 '17 at 21:26
Possible duplicate of [Drop all tables whose names begin with a certain string](http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string) — McNets, Apr 20 '17 at 21:26
Don't do it, too dangerous! You'll build a cool little script that will solve all of your problems and set it up as a database job and forget about it until one day, long after you've forgotten about it (or maybe after you have left), someone will come along and create a table named "TOPMOST_EARNERS" and you will be cursed when they find out your script has been wiping it out. Make a script that specifically lists the tables to be truncated that someone must eyeball once in a while to maintain and verify as a double-check. — Gary_W, Apr 20 '17 at 21:30

Greg · Answer 1 · 2017-04-20T21:37:11.887

2

Cut/paste this into your query window

select 'truncate table ' + quotename(OBJECT_SCHEMA_NAME(object_id)) + '.' + quotename(name)
from sys.tables
where name like '%PMO%'
order by name;

IF you are looking to automate this, then put pipe results into a table, and loop through the table, executing the TSQL one by one.

edited Apr 20 '17 at 21:37

answered Apr 20 '17 at 21:32

Greg

3,861
3
23
58

Doing this gives you a chance to review the script before running it. I recommend ordering it by name, to make that a bit easier. – Philip Kelley Apr 20 '17 at 21:35

score 0 · Accepted Answer · answered Apr 21 '17 at 18:30

So, after combining the code from the possible duplicate question, and the answer from Greg, here's the result that worked best:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'truncate table ' + quotename(OBJECT_SCHEMA_NAME(object_id)) + '.' + 
quotename(name)
FROM sys.tables
WHERE name like '%PMO%'
ORDER BY name;

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

Thanks to everyone for your help!!!

SQL query to delete all rows from tables where the table name contains a certain string

2 Answers2