Delete data form multiple unknown tables in SQL Server?

Question

I'm trying to delete specific data from any tables in my SQL Server. DB like this. I want to delete all Romios.

Dog.table
(NAME,AGE) = {(Romio,2), (Juliet,3)}

Cat.table
(NAME, Fur) = {(Romio,short), (Trump, bald)}

Cow.table
(Name, Mlik) = {(Romio, 10L), (Obama, 2L)}

Useless.table
(Use1, Use2) = {...}

So, I got tables list with the below code:

SELECT TABLE_NAME FROM information_schema.COLUMNS where COLUMN_NAME =  'NAME'

But, the next step doesn't work.

DELETE (SELECT TABLE_NAME FROM information_schema.COLUMNS where COLUMN_NAME =  'NAME') WHERE Name = 'Romio'

Yeah..... I know delete query only works for 1 table. That query has a problem.

How can I make it work?

you'll need dynamic sql. happy reading! http://www.sommarskog.se/dynamic_sql.html — Jeremy, Dec 21 '17 at 02:33
Ask the question correctly and that usually leads you to an answer - or at least a direction. You want to delete rows where some column (or maybe specific column) contains the mentioned value. So the first step is to find all occurrences, correct? That has been answered **many** times [example](https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008/19112606). — SMor, Dec 21 '17 at 14:12

score 0 · Accepted Answer · answered Dec 21 '17 at 13:24

Try this if you don't mind using dynamic TSQL, i.e. using EXEC

DECLARE @StrQuery nvarchar(MAX)
SELECT @StrQuery = ISNULL(@StrQuery, '') + 'DELETE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE NAME = ''Romio''  ' FROM information_schema.COLUMNS where COLUMN_NAME =  'NAME'
EXEC(@StrQuery)

Delete data form multiple unknown tables in SQL Server?

1 Answers1