If you ever worked around legacy databases, specially a one with more than a couple hundred tables, you might need some information about the core tables. I created a simple query, that will create the nodes of a Graph around the specified CoreTable.

Node levels:
…
-3 => Referenced in a -2 level table
-2 => Referenced in a -1 level table
-1 => Referenced in Core table
0 => Core table
1 => Referencing to Core table
2 => Referencing to a 1 level table
3 => Referencing to a 2 level table
…

DECLARE @coretable varchar(200) = 'CoreTable'; 

CREATE TABLE #TempDepthGraph 
(
	NodeLevel int,
	NodeTable varchar(200),
	NodeColumn varchar(200),
	RefrencedTable varchar(200),
	RefrencedColumn varchar(200),
	ID varchar(200)
);

INSERT INTO #TempDepthGraph (NodeLevel, NodeTable)
VALUES (0,@coretable);

DECLARE @levelstep int = 1; 
DECLARE @toanalyze int = 1; 

WHILE (@toanalyze > 0) 
BEGIN  

	INSERT INTO #TempDepthGraph (ID, NodeLevel, NodeTable, NodeColumn, RefrencedTable, RefrencedColumn)
	SELECT	obj.name, @levelstep, tab1.name, col1.name, tab2.name, col2.name
	FROM sys.foreign_key_columns fkc
	INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
	INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
	INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
	INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
	INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
	INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
	WHERE 
		NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND 
		EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab2.name AND NodeLevel >= 0) 

	SET @toanalyze = 
	(
		SELECT COUNT(1)
		FROM sys.foreign_key_columns fkc
		INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
		INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
		INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
		INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
		INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
		INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
		WHERE 
			NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND 
			EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab2.name AND NodeLevel >= 0) 
	)
	
   SET @levelstep = @levelstep+1;
END  


SET @levelstep = -1; 
SET @toanalyze = 1; 

WHILE (@toanalyze > 0) 
BEGIN  

	INSERT INTO #TempDepthGraph (ID, NodeLevel, NodeTable, NodeColumn, RefrencedTable, RefrencedColumn)
	SELECT	obj.name, @levelstep, tab2.name, col2.name, tab1.name, col1.name
	FROM sys.foreign_key_columns fkc
	INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
	INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
	INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
	INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
	INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
	INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
	WHERE 
		NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND 
		EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab1.name AND NodeLevel <= 0) 

	SET @toanalyze = 
	(
		SELECT COUNT(1)
		FROM sys.foreign_key_columns fkc
		INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
		INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
		INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
		INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
		INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
		INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
		WHERE 
			NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND 
			EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab1.name AND NodeLevel <= 0) 
	)
	
   SET @levelstep = @levelstep-1;
END  

SELECT * FROM #TempDepthGraph ORDER BY NodeLevel ASC

DROP TABLE #TempDepthGraph

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

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;

If you need to search in the content of all the stored procedures, here’s a quite good example to do it.

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%Search string%';

Well this will be a big script, so I don’t include it. This function creates a ShiftJis Decode-ed string into a classic unicode decoded string.
Use it wisely.
ShiftJisDecode