Looking for a special character in all tables

I received a rather interesting task recently. A customer wanted to find a special character in their database, and then remove it from all the places where it existed.

The query had to look in all tables in the whole database, in any column. I spent a couple of days thinking about how I would take on this task, without having to run a query on the customers server in “prime time” and block the employees using the database.

I ended up with the idea of using SQL server metadata to query the tables getting only the columns of character type. These columns could be used in a dynamic query where I could look for a special character. To avoid having a long running query loop through 250 tables and a lot of columns, I would create a table where I could log the table and schema names, and then set some type of flag when the table was queried. The I could write code to handle N tables in a timeslot where the customer didn’t have a lot of load on their server.

Actually I could create a stored procedure taking N of tables and then schedule execution using a SQL agent Job. When a match is found I would log the result in a log table. Then it would be a simple task to write a query to update the rows. With an idea of how to take on this task it was time to write some scripts. First I created the tables to log the results and to hold the table, schema names and the overall process.

USE master
GO
IF (OBJECT_ID( 'dbo.SearchTablesLog', 'u')) IS NOT NULL
DROP TABLE master.dbo.SearchTablesLog
CREATE TABLE master.dbo.SearchTablesLog
(
Id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
Table_Schema SYSNAME,
Table_Name SYSNAME,
Search_started DATETIME2,
Search_Completed DATETIME2,
Has_Error BIT DEFAULT 0,
Error_Message NVARCHAR(4000)
)
GO

The search_started column is timestamped when the table search is started and the search_completed column is timestamped when the search in the table is complete. If an error occurs, then the Has_Error column is set to 1 and the message is logged for debug purposes.

IF (OBJECT_ID( 'dbo.SearchTablesResults', 'u')) IS NOT NULL
DROP TABLE master.dbo.SearchTablesResults
CREATE TABLE master.dbo.SearchTablesResults
(
[PK COLUMN] NVARCHAR(MAX),
[COLUMN VALUE] NVARCHAR(MAX),
[COLUMN Name] sysname,
[TABLE SCHEMA] sysname,
[TABLE Name] sysname

)
GO

This table holds the results making it possible to write a query to update the tables where the character exists. Now its time to write the procedure to handle the search. Note the procedures have to be created on the database you want to search.

CREATE PROCEDURE dbo.SearchStringInTable(@SearchString NVARCHAR(MAX),
@Table_Schema sysname = 'dbo',
@Table_Name sysname,
@IsCharSearch BIT = 0)
AS
BEGIN

DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'');

IF @Columns IS NULL -- no character columns
RETURN -1;

-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'');

SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT '
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME COLLATE DATABASE_DEFAULT

WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
ORDER BY CU.ORDINAL_POSITION
FOR XML PATH('')),1,9,'');

IF @PkColumn IS NULL
SELECT @PkColumn = 'CAST(NULL AS nvarchar(max))';

-- set select statement using dynamic UNPIVOT
DECLARE @SQL NVARCHAR(MAX)
IF(@IsCharSearch = 1)
BEGIN
SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
' FROM
(SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
WHERE [Column Value] LIKE ''%' +@SearchString + '%'''
END
ELSE
BEGIN
SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
' FROM
(SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
WHERE [Column Value] LIKE ''%''+' +REPLACE(@SearchString, '''','') + '+''%'''
END
EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString;
END
GO

A couple of notes. The parameter @IscharSearch is used to control how the dynamic search SQL is created, if it’s a text search then the @SearchString parameter is inserted without any other modification. But if it’s a search for a special character like CHAR(13) then some modifications are necessary:

WHERE [Column Value] LIKE ''%''+' +REPLACE(@SearchString, '''','') + '+''%'''

The rest of the parameters are self-explanatory. Then the procedure is to handle the logic of running N tables and call the procedure SearchStringInTable

IF(OBJECT_ID('SearchTablesForString', 'P')) IS NOT NULL
DROP PROC SearchTablesForString
GO
CREATE PROC SearchTablesForString(@Take INT = 5,@SearchString NVARCHAR(MAX), @IsCharSearch BIT = 0 )
AS
BEGIN
DECLARE @Table_Name sysname, @Table_Schema sysname, @id INT
IF(OBJECT_ID('tempdb..##TablesToBeSearched', 'U')) IS NOT NULL
DROP TABLE #TablesToBeSearched
;With TheNextTables_CTE
AS(
SELECT row_number() OVER(order by id asc) RowNo, Id, Table_Schema, Table_Name
FROM master.dbo.SearchTablesLog L
WHERE L.Search_Completed IS NULL AND Has_Error <> 1
)
SELECT Id, Table_Schema, Table_Name
INTO #TablesToBeSearched
FROM TheNextTables_CTE
WHERE RowNo <= @take
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT Id, Table_Schema, Table_Name
FROM #TablesToBeSearched


OPEN curAllTables
FETCH curAllTables
INTO @id, @Table_Schema, @Table_Name
WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
BEGIN
UPDATE L
SET Search_started = GETDATE()
FROM master.dbo.SearchTablesLog L
WHERE l.Id = @Id

BEGIN TRY
INSERT master.dbo.SearchTablesResults ([PK COLUMN], [Column Value], [Column Name], [Table Schema], [Table Name])
EXECUTE SearchStringInTable @SearchString, @Table_Schema, @Table_Name, @IsCharSearch

UPDATE L
SET Search_Completed = GETDATE()
FROM master.dbo.SearchTablesLog L
WHERE l.Id = @Id
END TRY
BEGIN CATCH
/* The execution has error log error message */
UPDATE L
SET Search_Completed = null,
Has_Error = 1,
Error_Message = ERROR_MESSAGE()
FROM master.dbo.SearchTablesLog L
WHERE l.Id = @Id
END CATCH

FETCH curAllTables
INTO @id,@Table_Schema, @Table_Name
END -- while
CLOSE curAllTables
DEALLOCATE curAllTables
END
GO

Beside the parameters which are the same as in the previous procedure, a new parameter @Take is used, the number indicates how many tables should be handled in this run, the tables are timestamped in search_Complete column when completed. To prepare for initial run the table SearchTableLog has to be populated.

INSERT INTO master.dbo.SearchTablesLog (Table_Schema, Table_Name)
SELECT Table_Schema, Table_Name
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY Table_Schema, Table_Name
GO

Now we are all set, and we can run the procedure from a Query or we can make an SQL Agent Job to run the Query at a schedule where the system isn’t on a heavy load.

DECLARE @SearchString NvARCHAR(MAX) = 'CHAR(13)'
EXEC SearchTablesForString 10, @SearchString, 0

Lets give it a try with an demo.
I have created the procedures in the database I want to search (Demo)

findspecialchar_sql_2

I have the following table

CREATE TABLE LookForText(
Id int IDENTITY(1,1) PRIMARY KEY,
TextCol Char(10),
Number Bigint DEFAULT(1)
)

And in there I have some rows

findspecialchar_sql_1

In column 6 there is a CHAR(13)

INSERT iNTO LookForText (TextCol) Values('Yep' +CHAR(13))

In the master I have these tables

findspecialchar_sql_3

And in the SearchTablesResult my only table in the database is present

findspecialchar_sql_9

First lets look for the CHAR(13) which should be in row 6 I run the stored procedure with 0 in the parameter IsCharSearch, because I look for a special character

DECLARE @SearchString NvARCHAR(MAX) = 'CHAR(13)'
EXEC SearchTablesForString 10, @SearchString, 0

Lets look at the result The SearchTablesLog is updated

findspecialchar_sql_4

And now for the results

findspecialchar_sql_6

And, as expected, column 6 is found and the column TextCol is found as the column having the value. And then I can run this Query to get the update statement:

DECLARE @SearchString NvARCHAR(MAX) = 'CHAR(13)'
;WITH MyTable_CTE
AS(
SELECT
[COLUMN VALUE],
[COLUMN Name],
[TABLE SCHEMA],
[TABLE Name]
FROM [master].[dbo].[SearchTablesResults]
GROUP BY [COLUMN VALUE],[COLUMN Name],[TABLE SCHEMA],[TABLE Name]
)
SELECT
[COLUMN VALUE],
[COLUMN Name],
[TABLE SCHEMA],
[TABLE Name],
CONCAT('UPDATE T ', 'SET ', QUOTENAME([COLUMN Name]) , '=REPLACE(',QUOTENAME([COLUMN Name]),',',@SearchString, ','''')', ' FROM ',QUOTENAME([TABLE SCHEMA]),'.' ,QUOTENAME([TABLE Name]) , ' AS T WHERE ',QUOTENAME([COLUMN Name]) ,' LIKE ''%''+' +REPLACE(@SearchString, '''','') + '+''%''') [LetsMakeItRight]
FROM MyTable_CTE

This statement does group the columns, then we will get one update statement per table, column combination and not one for every row found in the table column combination. Anyway, here is the result:

findspecialchar_sql_7

If I want to find the row with the FindMe text then I will run the procedure like so

DECLARE @SearchString NvARCHAR(MAX) = 'FindMe' 
EXEC SearchTablesForString 10, @SearchString, 1

And no surprise here, one record is returned

findspecialchar_sql_8

Thanks for reading. If you have any questions, troubles with your SQL server, queries, or environment, please don’t hesitate to call me or one of my colleagues at Unit IT phone: 88 333 333.
 
Here is the complete script:
 
/* Change database to the database to be searched for a string 
Created: 28-01-2019
By: Jacob Saugmann
Search and replace:
[DatabaseName] = The database you want to search in
*/
USE Demo
GO
IF(OBJECT_ID('spSearchStringInTable', 'P')) IS NOT NULL
DROP PROCEDURE spSearchStringInTable
GO

CREATE PROCEDURE dbo.spSearchStringInTable(@SearchString NVARCHAR(MAX),
@Table_Schema sysname = 'dbo',
@Table_Name sysname,
@IsCharSearch BIT = 0)

AS
BEGIN

DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'');

IF @Columns IS NULL -- no character columns
RETURN -1;

-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'');

SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT '
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME COLLATE DATABASE_DEFAULT

WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
ORDER BY CU.ORDINAL_POSITION
FOR XML PATH('')),1,9,'');

IF @PkColumn IS NULL
SELECT @PkColumn = 'CAST(NULL AS nvarchar(max))';

-- set select statement using dynamic UNPIVOT
DECLARE @SQL NVARCHAR(MAX)
IF(@IsCharSearch = 1)
BEGIN
SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
' FROM
(SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
WHERE [Column Value] LIKE ''%' +@SearchString + '%'''
END
ELSE
BEGIN
SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
' FROM
(SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
WHERE [Column Value] LIKE ''%''+' +REPLACE(@SearchString, '''','') + '+''%'''
END
--print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString;
END
GO
/* Creates a log table inside master */
USE master
GO
IF (OBJECT_ID( 'dbo.SearchTablesLog', 'u')) IS NOT NULL
DROP TABLE master.dbo.SearchTablesLog
CREATE TABLE master.dbo.SearchTablesLog
(
Id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
Table_Schema SYSNAME,
Table_Name SYSNAME,
Search_started DATETIME2,
Search_Completed DATETIME2,
Has_Error BIT DEFAULT 0,
Error_Message NVARCHAR(4000)
)
GO

IF (OBJECT_ID( 'dbo.SearchTablesResults', 'u')) IS NOT NULL
DROP TABLE master.dbo.SearchTablesResults
CREATE TABLE master.dbo.SearchTablesResults
(
[PK COLUMN] NVARCHAR(MAX),
[COLUMN VALUE] NVARCHAR(MAX),
[COLUMN Name] sysname,
[TABLE SCHEMA] sysname,
[TABLE Name] sysname

)
GO

/* Change database to the database to be searched for a string */
USE Demo
GO
INSERT INTO master.dbo.SearchTablesLog (Table_Schema, Table_Name)
SELECT Table_Schema, Table_Name
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY Table_Schema, Table_Name
GO
/* DEBUG */
--SELECT * FROM master.dbo.SearchTablesLog
IF(OBJECT_ID('SearchTablesForString', 'P')) IS NOT NULL
DROP PROC SearchTablesForString
GO
CREATE PROC SearchTablesForString(@Take INT = 5,@SearchString NVARCHAR(MAX), @IsCharSearch BIT = 0 )
WITH ENCRYPTION
AS
BEGIN
DECLARE @Table_Name sysname, @Table_Schema sysname, @id INT
IF(OBJECT_ID('tempdb..##TablesToBeSearched', 'U')) IS NOT NULL
DROP TABLE #TablesToBeSearched
;With TheNextTables_CTE
AS(
SELECT row_number() OVER(order by id asc) RowNo, Id, Table_Schema, Table_Name
FROM master.dbo.SearchTablesLog L
WHERE L.Search_Completed IS NULL AND Has_Error <> 1
)
SELECT Id, Table_Schema, Table_Name
INTO #TablesToBeSearched
FROM TheNextTables_CTE
WHERE RowNo <= @take
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT Id, Table_Schema, Table_Name
FROM #TablesToBeSearched


OPEN curAllTables
FETCH curAllTables
INTO @id, @Table_Schema, @Table_Name
WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
BEGIN
UPDATE L
SET Search_started = GETDATE()
FROM master.dbo.SearchTablesLog L
WHERE l.Id = @Id

BEGIN TRY
INSERT master.dbo.SearchTablesResults ([PK COLUMN], [Column Value], [Column Name], [Table Schema], [Table Name])
EXECUTE spSearchStringInTable @SearchString, @Table_Schema, @Table_Name, @IsCharSearch

UPDATE L
SET Search_Completed = GETDATE()
FROM master.dbo.SearchTablesLog L
WHERE l.Id = @Id
END TRY
BEGIN CATCH
/* The execution has error log error message */
UPDATE L
SET Search_Completed = null,
Has_Error = 1,
Error_Message = ERROR_MESSAGE()
FROM master.dbo.SearchTablesLog L
WHERE l.Id = @Id
END CATCH

FETCH curAllTables
INTO @id,@Table_Schema, @Table_Name
END -- while
CLOSE curAllTables
DEALLOCATE curAllTables
END
GO
/*DEBUG*/
--DECLARE @SearchString NvARCHAR(MAX) = 'CHAR(13)'
--EXEC SearchTablesForString 10, @SearchString, 1
-- ;WITH MyTable_CTE
-- AS(
-- SELECT
-- [COLUMN VALUE],
-- [COLUMN Name],
-- [TABLE SCHEMA],
-- [TABLE Name]
-- FROM [master].[dbo].[SearchTablesResults]
-- GROUP BY [COLUMN VALUE],[COLUMN Name],[TABLE SCHEMA],[TABLE Name]
-- )
-- SELECT
-- [COLUMN VALUE],
-- [COLUMN Name],
-- [TABLE SCHEMA],
-- [TABLE Name],
-- CONCAT('UPDATE T ', 'SET ', QUOTENAME([COLUMN Name]) , '=REPLACE(',QUOTENAME([COLUMN Name]),',',@SearchString, ','''')', ' FROM ',QUOTENAME([TABLE SCHEMA]),'.' ,QUOTENAME([TABLE Name]) , ' AS T WHERE ',QUOTENAME([COLUMN Name]) ,' LIKE ''%''+' +REPLACE(@SearchString, '''','') + '+''%''') [LetsMakeItRight]
-- FROM MyTable_CTE