Find and delete all records with a specific string in a SQL Server database

Question

I'm trying to find and delete all records that contain a certain string in a database in SQL Server 2016 CTP 3. I found the awesome query below on this site, which nicely returns all the records that I need to delete. Is it possible to add a delete statement to the code to delete the records that were found? This might be a simple question but I've never written a script for SQL Server.

(Found at Find a string by searching all tables in SQL Server Management Studio 2008 ) .

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
          AND OBJECTPROPERTY(OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
              AND TABLE_NAME = PARSENAME(@TableName, 1)
              AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
              AND QUOTENAME(COLUMN_NAME) > @ColumnName)

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results

            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

Answer 1

By saying

I'm trying to find and delete all records that contain a certain string in a database...

Do you need to delete some records in a table, if a data element in a column contains a certain string ? if so you can use :

Delete from yourTable where relevantField like '%YourSearchText%' 

or you want to check all the columns of all the tables and delete any record (the whole row) satisfying that condition, you may have to use the one you posted and modify it.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Replace

DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

With

DECLARE @Results TABLE (TableName nvarchar(370), ColumnName nvarchar(370))

Replace

INSERT INTO @Results 
Exec ...

With

INSERT INTO @Results
Select @TableName as TableName, @ColumnName as ColumnName 

Replace

SELECT ColumnName, ColumnValue FROM @Results

With

Select @execSQL = 
'Delete from [' + TableName + '] where [' + ColumnName + '] like ''%' + @s + '%'''  from @Results Group by TableName, ColumnName
Exec (@execSQL)

In the declaration - declare the variable @execSQL, as

Declare @execSQL nvarchar(max)