Search for a specific value in any column

If you wonder, where is that string you see on the screen, have no idea from where it came from, and debugging would take hours (in large legacy systems), than here is my solution.
There is an outcommented line, in which you can limit the searches for specific column types. Sometimes yome data types are dying in conversion, and you need to limit also the searches these ways.
Just repalce the value in the first line, to make it work.

DECLARE @searchstring nvarchar(max) = '%SearchData%';

DECLARE @tablename nvarchar(max), @columnname nvarchar(max);
DECLARE @sql nvarchar(max) 
CREATE TABLE #TemporaryStatisticsTable
(
	tablename nvarchar(max),
	columnname nvarchar(max),
	content nvarchar(max),
)

DECLARE table_cursor CURSOR FOR   
SELECT  name
FROM 	sys.tables

OPEN table_cursor  

FETCH NEXT FROM table_cursor   
INTO @tablename  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    
	DECLARE column_cursor CURSOR FOR   
	SELECT c.name from sys.columns c 
	INNER JOIN sys.tables t ON t.object_id = c.object_id AND t.name = @tablename
	--WHERE c.system_type_id IN (175, 167, 35)

	OPEN column_cursor  

	FETCH NEXT FROM column_cursor   
	INTO @columnname  

	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		
		SET @sql = 'INSERT  #TemporaryStatisticsTable (tablename, columnname, content)
					SELECT ''' + @tablename + ''', ''' + @columnname + ''', 
						CAST(' + @columnname + ' as nvarchar(max))
					FROM ' + @tablename + '
					WHERE CAST(' + @columnname + ' as nvarchar(max)) LIKE ''' + @searchstring + '''
					'
		exec sp_executesql @sql

	    FETCH NEXT FROM column_cursor   
		INTO @columnname  
	END   
	CLOSE column_cursor;  
	DEALLOCATE column_cursor; 
	
    FETCH NEXT FROM table_cursor   
    INTO @tablename  
END   
CLOSE table_cursor;  
DEALLOCATE table_cursor; 

SELECT * FROM #TemporaryStatisticsTable

DROP TABLE #TemporaryStatisticsTable

Search for a specific value in a common named column

Sometimes, you need to find some data in a specificly named column, like what changed by a user, in the “LAST_CHANGED_BY” columns.
If you name the column, and the specific value you search, this script gives you a list of tables of how many rows any tables have with the matching values in matching columns.

DECLARE @columnname varchar(100) = 'LAST_CHANGED_BY';
DECLARE @columnvalue nvarchar(max) = 'USERNAME';

CREATE TABLE #TemporaryData
(
	TableName varchar(1000),
	RowNum int
);

DECLARE @TablesToSearch TABLE 
(
	ColumnName varchar(1000),
	TableName varchar(1000)
);

INSERT INTO @TablesToSearch (ColumnName, TableName)
SELECT c.name  AS 'ColumnName' ,t.name AS 'TableName'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = @columnname;

DECLARE @tablename nvarchar(max), @sql nvarchar(max);

DECLARE table_cursor CURSOR FOR   
SELECT  TableName
FROM 	@TablesToSearch;

OPEN table_cursor  ;

FETCH NEXT FROM table_cursor   
INTO @tablename  ;

WHILE @@FETCH_STATUS = 0  
BEGIN  

	SET @sql = 'INSERT  #TemporaryData (TableName, RowNum)
				SELECT ''' + @tablename + ''', COUNT(1)
				FROM ' + @tablename + '
				WHERE CAST(' + @columnname + ' as nvarchar(max)) = ''' + @columnvalue + ''';
				'
	exec sp_executesql @sql;
	
    FETCH NEXT FROM table_cursor   
    INTO @tablename  
END   
CLOSE table_cursor;  
DEALLOCATE table_cursor; 
	
SELECT * FROM #TemporaryData ORDER BY RowNum DESC;
DROP TABLE #TemporaryData;

Misconception about nvarchar(max)

I had a stupid misconception about nvarchar(max) for a couple years now, and it was a shock, when it came down.

Some say, that nvarchar(max) is the same as nvarchar(4000). It is exactly that, and you can not store more, than 4000 characters in it.

But thats not true.

The basics of this is MSSQL normally stores the nvarchar(max) data in an nvarchar(4000) place, BUT if you have a data bigger than that, this storage will become a pointer, and MSSQL will store it outside of the table. It will make it slower to search, harder to index, but it will store the data.

It’s important, because it will still be faster, than ntext.

T-SQL function to HTML Decode

Sometimes you need to decode some text on the database side.
Sometimes you don’t.
In case you need to, here is one special function.

There will be some additions to it, to a different encode type soon.


GO
IF OBJECT_ID('dbo.fn_HTMLDecode') IS NOT NULL BEGIN DROP FUNCTION dbo.fn_HTMLDecode END
GO
CREATE FUNCTION dbo.fn_HTMLDecode(
    @vcWhat NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @vcResult NVARCHAR(MAX);
    DECLARE @siPos INT ,@vcEncoded NVARCHAR(9) ,@siChar INT;
    SET @vcResult = RTRIM(LTRIM(CAST(REPLACE(@vcWhat COLLATE Latin1_General_BIN, CHAR(0), '') AS NVARCHAR(MAX))));
    SELECT @vcResult = REPLACE(REPLACE(@vcResult, ' ', ' '), ' ', ' ');
    IF @vcResult = '' RETURN @vcResult;

    declare @s varchar(35);
    declare @n int; set @n = 6;
    declare @i int;

    while @n > 2
    begin
        set @s = '';
        set @i=1;
        while @i<=@n
        begin
            set @s = @s + '[0-9]';
            set @i = @i + 1;
        end
        set @s = '%&#' + @s + '%';
        SELECT @siPos = PATINDEX(@s, @vcResult);
        WHILE @siPos > 0
        BEGIN
            SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, @n+3)
                ,@siChar = CAST(SUBSTRING(@vcEncoded, 3, @n) AS INT)
                ,@vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
                ,@siPos = PATINDEX(@s, @vcResult);
        END
        set @n = @n - 1;
    end

	select @vcResult = REPLACE(REPLACE(@vcResult, NCHAR(160), ' '), CHAR(160), ' ');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult, '&', '&'), '"', '"'), '<', '<'), '>', '>'), '&amp;', '&'),'”','”'),'„','„'),'–','–'),'—','—');

	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Š','Š') COLLATE Latin1_General_BIN,'š','š') COLLATE Latin1_General_BIN,'Ç','Ç') COLLATE Latin1_General_BIN,'ç','ç');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult,'À','À') COLLATE Latin1_General_BIN,'à','à') COLLATE Latin1_General_BIN,'Á','Á') COLLATE Latin1_General_BIN,'á','á') COLLATE Latin1_General_BIN,'Â','Â') COLLATE Latin1_General_BIN,'â','â') COLLATE Latin1_General_BIN,'Ã','Ã') COLLATE Latin1_General_BIN,'ã','ã') COLLATE Latin1_General_BIN,'Ä','Ä') COLLATE Latin1_General_BIN,'ä','ä') COLLATE Latin1_General_BIN,'Å','Å') COLLATE Latin1_General_BIN,'å','å') COLLATE Latin1_General_BIN,'Æ','Æ') COLLATE Latin1_General_BIN,'æ','æ');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'È','È') COLLATE Latin1_General_BIN,'è','è') COLLATE Latin1_General_BIN,'É','É') COLLATE Latin1_General_BIN,'é','é') COLLATE Latin1_General_BIN,'Ê','Ê') COLLATE Latin1_General_BIN,'ê','ê') COLLATE Latin1_General_BIN,'Ë','Ë') COLLATE Latin1_General_BIN,'ë','ë');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ì','Ì') COLLATE Latin1_General_BIN,'ì','ì') COLLATE Latin1_General_BIN,'Í','Í') COLLATE Latin1_General_BIN,'í','í') COLLATE Latin1_General_BIN,'Î','Î') COLLATE Latin1_General_BIN,'î','î') COLLATE Latin1_General_BIN,'Ï','Ï') COLLATE Latin1_General_BIN,'ï','ï');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ò','Ò') COLLATE Latin1_General_BIN,'ò','ò') COLLATE Latin1_General_BIN,'Ó','Ó') COLLATE Latin1_General_BIN,'ó','ó') COLLATE Latin1_General_BIN,'Ô','Ô') COLLATE Latin1_General_BIN,'ô','ô') COLLATE Latin1_General_BIN,'Õ','Õ') COLLATE Latin1_General_BIN,'õ','õ') COLLATE Latin1_General_BIN,'Ö','Ö') COLLATE Latin1_General_BIN,'ö','ö') COLLATE Latin1_General_BIN,'Ø','Ø') COLLATE Latin1_General_BIN,'ø','ø');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ù','Ù') COLLATE Latin1_General_BIN,'ù','ù') COLLATE Latin1_General_BIN,'Ú','Ú') COLLATE Latin1_General_BIN,'ú','ú') COLLATE Latin1_General_BIN,'Û','Û') COLLATE Latin1_General_BIN,'û','û') COLLATE Latin1_General_BIN,'Ü','Ü') COLLATE Latin1_General_BIN,'ü','ü');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ð','Ð') COLLATE Latin1_General_BIN,'ð','ð') COLLATE Latin1_General_BIN,'Ñ','Ñ') COLLATE Latin1_General_BIN,'ñ','ñ') COLLATE Latin1_General_BIN,'Ý','Ý') COLLATE Latin1_General_BIN,'ý','ý') COLLATE Latin1_General_BIN,'Þ','Þ') COLLATE Latin1_General_BIN,'þ','þ') COLLATE Latin1_General_BIN,'ß','ß');

    RETURN @vcResult;
END

Background worker to abort

So…

Background Worker is crazy, if you want to use a cancel on it. If you have a deep dive into a long running third party library, it is basically useless, because you can’t really use an easy way to abort. You can call .Cancel(), but you can’t implement the canceling logic inside a third party library. So what you can do?

There are many ways, but here’s a very simple one.

Make the whole control abortable, by attaching to the thread it uses, and just create an abort function, to drop the whole thread.

Class:

public class AbortableBackgroundWorker : BackgroundWorker
    {

        private Thread thread;

        protected override void OnDoWork(DoWorkEventArgs e)
        {
            thread = Thread.CurrentThread;
            try
            {
                base.OnDoWork(e);
            }
            catch (ThreadAbortException)
            {
                e.Cancel = true;
                Thread.ResetAbort();
            }
        }

        public void Abort()
        {
            if (thread != null)
            {
                thread.Abort();
                thread = null;
            }
        }
    }

Implementation:

	abortable.Abort();
	abortable.Dispose();