Here’s a simple ( and probably the most common ) way to search across all your tables, and columns within those tables; if you are looking for a particular name either on the table side, or the column side. Use the following 2 tables to make a basic query. You can do this in every database.
They are both self explanatory, but the .Columns is more comprehensive. Anyway; on with the logic.
use [MyDatabase] select table_schema , table_name , column_name from information_schema.columns where table_name like '%Something%' order by table_name, column_name asc