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;