0

Can someone suggest, How to find object names with help of column name in BigQuery?

In Teradata, we can get objects(in all databases) in which particular column used by using dbc.columnsv. We can use dataset.INFORMATION_SCHEMA.COLUMNS in BigQuery to find out such object list. But, it will give object names belonging to one particular database. So, my question is how to find all objects in all databases where one particular column is used in BigQuery?

Thanks in advance.

Santosh Suplepatil
  • 11
  • 3

1 Answers1

1

This should work

DECLARE
  col_to_search string DEFAULT "col1";
DECLARE
  sql_stmts string;
SET
  sql_stmts = (
  SELECT
    ARRAY_TO_STRING((
      SELECT
        ARRAY (
        SELECT
          CONCAT("SELECT * FROM `",sc.catalog_name,"`.",schema_name,".INFORMATION_SCHEMA.COLUMNS WHERE column_name = ","'",col_to_search,"'")
        FROM
          `planar-effect-325211`.INFORMATION_SCHEMA.SCHEMATA sc ))," UNION ALL ") );

EXECUTE IMMEDIATE sql_stmts;
Jofre
  • 3,718
  • 1
  • 23
  • 31
Mr.Batra
  • 787
  • 1
  • 5
  • 11
  • Thanks for your answer. However, I am not familiar with PL/SQL blocks. Can you please tell me what does this 'sql_statmts' mean? – Santosh Suplepatil Sep 20 '21 at 09:45
  • "sql_stmts" in the code above is a string variable which stores the string generated by the SQL query. Here below are the big query documentation links for understanding EXECUTE IMMEDIATE and Array functions included in script. [link] https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#execute_immediate [link] https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions – Mr.Batra Sep 20 '21 at 10:20
  • Please mark as answer if it is what you are looking for. – Mr.Batra Sep 20 '21 at 10:23
  • What 'sql_statmts' I have to provide after IMMEDIATE keyword? – Santosh Suplepatil Sep 21 '21 at 06:38
  • DECLARE sql_stmts string; The above line of code is a variable declaration, this variable stores results coming from select. i.e. SET sql_stmts = ( SELECT ARRAY_TO_STRING(..... So, sql_stamts is nothing but a variable which holds the SQL queries to run. And Execute imediate runs those sql queries for you dynamically. – Mr.Batra Sep 21 '21 at 07:11
  • I am asking because, when I run 'EXECUTE IMMEDIATE sql_stmts' command, I am getting below error: 'Empty 'EXECUTE IMMEDIATE' sql string can not be executed. – Santosh Suplepatil Sep 21 '21 at 12:31
  • Did u changed the col_to_search var to ur column name? ````col_to_search string DEFAULT "col1"```` – Mr.Batra Sep 21 '21 at 13:08
  • Yes. I have changed column name but, its not working. col_to_search string DEFAULT "sender_name" – Santosh Suplepatil Sep 21 '21 at 18:35
  • Not sure, why this is happening. It worked for me. Could you paste your query? – Mr.Batra Sep 22 '21 at 06:33
  • DECLARE col_to_search string DEFAULT "comment"; DECLARE sql_stmts string; SET sql_stmts = ( SELECT ARRAY_TO_STRING(( SELECT ARRAY ( SELECT CONCAT("SELECT * FROM `",sc.catalog_name,"`.",schema_name,".INFORMATION_SCHEMA.COLUMNS WHERE column_name = ","'",col_to_search,"'") FROM INFORMATION_SCHEMA.SCHEMATA sc ))," UNION ALL ") ); EXECUTE IMMEDIATE sql_stmts; – Santosh Suplepatil Sep 22 '21 at 10:20
  • Here it is..... – Santosh Suplepatil Sep 22 '21 at 10:21
  • it runs fine for me. I ran your pasted query. make sure u have required accesses to query the INFORMATION_SCHEMA.SCHEMATA view. – Mr.Batra Sep 22 '21 at 10:31
  • I have access to both the objects. – Santosh Suplepatil Sep 22 '21 at 12:43