Retrieve schema name from view definition

Question

In Snowflake, I retrieve different views with the following SQL query:

SELECT * FROM "myDatabase"."mySchema"."VIEWS"

That returns a table with these columns notably:

TABLE_ID
TABLE_NAME
TABLE_SCHEMA_ID
TABLE_SCHEMA
TABLE_CATALOG_ID
TABLE_CATALOG
TABLE_OWNER
VIEW_DEFINITION

For each VIEW_DEFINITION column entries, I am trying to extract all the strings <Schema_Name>.<View_Name> (or at least the <Schema_Name>).

Is it possible to do that with a SQL query (or by any other way)?

---

Edit

The table I obtain using the initial query is as follows:

TABLE_ID	TABLE_NAME	TABLE_SCHEMA_ID	TABLE_SCHEMA	TABLE_CATALOG_ID	TABLE_CATALOG	TABLE_OWNER	VIEW_DEFINITION
0001	MY_TABLE_NAME	99	MY_TABLE_SCHEMA	20	PMY_TABLE_CATALOG	MY_OWNER_VIEWS_ADMIN	…

where the VIEW_DEFINITION column contains queries like the one below:

"CREATE OR REPLACE VIEW My_Table_Schema_VIEWS.My_Table_Name AS
WITH STUDY_SITE_SCOPE AS (
SELECT
    ...
FROM (
       SELECT
          A.SUBJECT_NUMBER
        , A.SUBJECT_STATUS
       FROM <Schema_Name>.<View_Name_1> X
       JOIN <Schema_Name>.<View_Name_2> Y
       ...
     )
JOIN (
       SELECT
          ...
       FROM <Schema_Name>.<View_Name_3> X
       JOIN <Schema_Name>.<View_Name_4> Y
         ...
      )
..."

From this VIEW_DEFINITION I am trying to extract all the <Schema_Name>.<View_Name_XX> strings (or at least the <Schema_Name>).

Answer 1

I assume you want to get all base schemas your current view is built on top of.

To answer your question short: Yes, it is.

Maybe the following procedure is giving you an idea on how to solve it via SQL or a Stored Procedure:

1. Query the view definition
2. Search for all strings within the view definition that follow the "FROM" or "JOIN" clause
3. Extract them and probably check for the database name in front of the schema name

Answer 2

You can use information_schema.tables:

select t.*
from information_schema.tables t
where t.table_type = 'VIEW'