Is there a way to search all object types in snowflake

Question

I have a UDF/Table/View/Proc that is called abc.def.efg

I don't know what type of object it is.

I want to search for all objects of all types in a schema/db/account.

Is there a way to do that?

If I know it's a user function, I can search

show user functions in account

If I know it's a procedure I can search

show procedures in my_db.my_schema

Is there a way to search

show all things in my_db.my_schema / account / etc

I believe show objects in only shows tables and views

show objects in ...

Answer 1

There is no easy way to do what you want, but here is an ugly workaround that will need additional tweaking if you have multiple databases or need to bring in additional metadata. You can turn this into a table valued function to hide the abstractions

with cte (type,name,location) as

(select 'UDF', function_name, function_catalog||'.'||function_schema
 from db.information_schema.functions
union all
 select 'PROCEDURE', procedure_name, procedure_catalog||'.'||procedure_schema
 from db.information_schema.procedures
union all
 select table_type, table_name, table_catalog||'.'||table_schema
 from db.information_schema.tables --includes both tables and views
 where table_schema<>'INFORMATION_SCHEMA')

select *
from cte
where name like '%abc%'

A more dynamic and perhaps cleaner version using result_scan. Note that the parameter to last_query_id needs to align with the order in which you run the show command

show functions in account;
show procedures in account;
show objects  in account;


with cte (type,name,location) as

(select 'UDF', "name", "catalog_name"||'.'||"schema_name" 
 from table(result_scan(last_query_id(-3)))
union all
 select 'PROCEDURE', "name", "catalog_name"||'.'||"schema_name"
 from table(result_scan(last_query_id(-2)))
union all
 select "kind", "name", "database_name"||'.'||"schema_name"
 from table(result_scan(last_query_id(-1))))

select *
from cte
where name ilike '%abc%'