CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sqlsp-executesql
storm97
storm97

Reputation: 43

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

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

Upvotes: 1

Views: 98

Answers (1)

Charlieface
Charlieface

Reputation: 71544

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;

Upvotes: 1

Related Questions