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

2 thoughts on “Search for a specific value in any column”

  1. No one can refuse the value of your article, but should you add more picture for reader can easily understand about what you want to transmit. Anyways, thank for your shared. Click Here

  2. I’ve been surfing on-line greater than 3 hours nowadays,
    yet I never discovered any attention-grabbing article
    like yours. It is pretty worth enough for me. In my opinion,
    if all site owners and bloggers made good content as you
    did, the internet will likely be a lot more helpful than ever
    before.

  3. I’ve been browsing on-line more than 3 hours today, but I by no means found any fascinating article like yours.

    It is pretty price enough for me. Personally, if all website owners and bloggers made
    excellent content material as you probably did, the internet
    can be a lot more useful than ever before.

  4. Admiring the time and energy you put into your website and in depth inflrmation you offer.
    It’s awesome to come across a blog every once in a wwhile
    that isn’t the same unwanted rehashed material.
    Wonderful read! I’ve savedd your site and I’m including your RSS feeds to
    my Google account.