Find tables with specific columns across databases in snowflake?

If you simply want to specify WHICH database to query, you can fully-qualify the reference to the INFORMATION_SCHEMA.TABLES view as follows:

select t.table_schema, 
       t.table_name
from DATABASE_NAME_TO_SEARCH.information_schema.tables t
inner join information_schema.columns c on 
       c.table_schema = t.table_schema and c.table_name = t.table_name
where t.table_type = 'BASE TABLE'
       and column_name ='N_NAME'
order by t.table_schema, 
       t.table_name;

If you want to scan across ALL databases within a Snowflake account, you can do it with the following PAIR of commands:

SHOW COLUMNS LIKE 'COLUMN_NAME_HERE' IN ACCOUNT
;
SELECT "database_name" AS TABLE_DB
      ,"schema_name" AS TABLE_SCHEMA
      ,"table_name" AS TABLE_NAME
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE "kind" = 'COLUMN'
 ORDER BY 1, 2, 3
;

One option is to use the database SNOWFLAKE.ACCOUNT_USAGE:

select table_schema, table_name, column_name
from snowflake.account_usage.columns
where deleted is null
and column_name='RIP4'

Most users don't have access to it, so you might need to grant the privileges first:

use role accountadmin;
grant imported privileges on database snowflake to role sysadmin;

https://docs.snowflake.com/en/sql-reference/account-usage.html

An alternative way to get the tables that contain a specific column name is the following:

show columns like 'COLUMN_NAME' in schema "DB_NAME"."SCHEMA_NAME"

In the above example, we search for all tables under the database "DB_NAME" and under the schema "SCHEMA_NAME" that contain the column called "COLUMN_NAME".

You can have a look at the Show Columns Snowflake Documentation. The full syntax is:

SHOW COLUMNS [ LIKE '<pattern>' ]
             [ IN { ACCOUNT | DATABASE [ <database_name> ] | SCHEMA [ <schema_name> ] | TABLE | [ TABLE ] <table_name> | VIEW | [ VIEW ] <view_name> } ]

Note that if you specify the keyword ACCOUNT, then the command retrieves records for all schemas in all databases of the current account.

Source: Predictive Hacks