CODEWITHSUNDEEP
sqlgoogle-bigqueryteradata
Abhishek R
Abhishek R

Reputation: 4763

How to find all tables and datasets/databases which have a specific column name in big query

I want to find a specific column name across all tables across all datasets/databases in Big Query.

In Teradata it can be done by running the following:

SELECT 
    DatabaseName, 
    TableName, 
    ColumnName 
FROM 
    DBC.Columns 
WHERE 
    ColumnName LIKE '%sender_country%' 
    AND DatabaseName NOT LIKE '%test%' 
    AND tablename LIKE '%sender%'

How can the same thing done be in Big Query?

I know we can look for different columns inside a particular dataset by running the following:

SELECT
    ddl 
FROM
    project-name.my_database_name.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%'

But the above query limits the search to only one dataset/database (in this example my_database_name).

How do I extend the search across all datasets?

Upvotes: 0

Views: 1291

Answers (1)

Abhishek R
Abhishek R

Reputation: 4763

I found the solution is to replace the dataset name with region-us instead.

The below works for looking up across tables and datasets

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%'

The below works for views:

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.VIEWS
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%'

Upvotes: 3

Related Questions