Search/delete a specific string in a defined column of all tables of all databases

Question

I am looking for a way to search or delete a certain string in a certain column of all tables of all databases of a server. The name of the column in which to search for the specified string starts as CNUM (i.e. columns CNUML, CNUMX, CNUM...)

In detail: a procedure that has two input parameters, one is the searched string and the other is a 1/0 parameter, where 1 means displaying a table containing the names of all tables that have the searched string in the specified column. The resulting table has the structure - example:

database	table	column
Database1	Table1	CNUMX

and 0 means that the procedure does not display the table but deletes the specified string from the specified column of all tables where the string occurs

Thank you for any help, as I am not able to handle this problem myself

please could the question be reopened, it differs from the linked question for example in that it is about deleting the search string from all tables where it occurs — storm97, May 23 '23 at 09:24
Can't you just modify it to `DELETE... WHERE` instead of `SELECT`? Or perhaps `UPDATE ... Column = REPLACE(Column, 'CNUM', '')` if that's what you mean by delete? Happy to reopen if you can say why it is that much different from the very wel written answers there? — Charlieface, May 23 '23 at 10:19
@Charlieface the problem is that I don't know how to modify the script in the linked question so that it checks only the columns whose name starts with 'CNUM' and possibly (if the parameter 0 is selected) deletes the searched records — storm97, May 23 '23 at 10:26
What do you mean by "delete" do you want to `DELETE` the whole row? — Charlieface, May 23 '23 at 10:37

Charlieface · Accepted Answer · 2023-05-23T12:04:52.507

According to your comments, you want to delete all rows matching this string in all such columns in the database. So just build up a string of all that using STRING_AGG and the system tables.

DECLARE @search varchar(1000) = 'YourSearchTerm';

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG('
DELETE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
WHERE ' + c.whereClause + ';
', '')
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
CROSS APPLY (
    SELECT whereClause = CAST(STRING_AGG(QUOTENAME(c.name) + ' = @search', ' OR ') AS nvarchar(max))
    FROM sys.columns c
    WHERE c.object_id = t.object_id
      AND c.name LIKE 'CNUM%'
    GROUP BY ()   -- need this in order to exclude tables with 0 columns
) c;

PRINT @sql; -- your friend

-- EXEC sp_executesql @sql,
--   N'@search varchar(1000)',
--   @search = @search;

-- uncomment when you got the right commands

If the data types or lengths are different then you might want a cast as well.

If you want this to work on all databases then you need dynamic-over-dynamic.

Place all the above code into a @sql variable, escaping all ' with ''. Then run a cursor over all databases, executing for each one EXEC database.sys.sp_executesql @sql

DECLARE @sql nvarchar(max) = '
DECLARE @search varchar(1000) = ''YourSearchTerm'';

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(''
DELETE '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
WHERE '' + c.whereClause + '';
'', '''')
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
CROSS APPLY (
    SELECT whereClause = CAST(STRING_AGG(QUOTENAME(c.name) + '' = @search'', '' OR '') AS nvarchar(max))
    FROM sys.columns c
    WHERE c.object_id = t.object_id
      AND c.name LIKE ''CNUM%''
    GROUP BY ()   -- need this in order to exclude tables with 0 columns
) c;


PRINT @sql; -- your friend

EXEC sp_executesql @sql,
  N''@search varchar(1000)'',
  @search = @search;
';


DECLARE @proc nvarchar(1000);
DECLARE @crsr CURSOR;
SET @crsr = CURSOR FORWARD_ONLY STATIC FOR
    SELECT QUOTENAME(name) + '.sys.sp_executesql'
    FROM sys.databases
    WHERE database_id > 4;  -- not system databases
OPEN @crsr;
WHILE 1=1
BEGIN
    FETCH NEXT FROM @crsr INTO @proc;
    IF @@FETCH_STATUS <> 0
        BREAK;

    EXEC @proc @sql;
END;

It completed with following error - The multi-part identifier "s.name" could not be bound. — storm97, May 23 '23 at 10:58
I changed it immediately after posting to add the join, copy the code again — Charlieface, May 23 '23 at 10:59
This will work on all schemas already, for all databases see edits — Charlieface, May 23 '23 at 11:52
'Place all the above code into a @sql variable' i am not sure how you mean it, please, could you modify the first code? — storm97, May 23 '23 at 12:01
Thank you, it looks good, but I would also like to ask how to get information about the database into the delete sentence in the first code? — storm97, May 23 '23 at 14:15
DELETE '' + QUOTENAME(s.name) - There is written schema name, but i need database name before schema name — storm97, May 23 '23 at 16:53
You don't need it, the whole thing will get executed in the correct database context due to the outer `EXEC @proc`. Try and change the outer dynamic SQL to `SELECT DB_NAME()` and you will see what I mean, see eg https://dbfiddle.uk/AMKDz_rq — Charlieface, May 23 '23 at 19:57

Search/delete a specific string in a defined column of all tables of all databases

1 Answers1