Select table name as a column in BigQuery

Question

I have a table in BigQuery, say table1

I want to be able to write

SELECT table1.table_name as Table_Name from table_name

How can I do this?

Can you explain why you need this? Usually, when the need for this sort of queries arises, its indicative of a sub-optimal approach. — HoneyBadger
I want to count the rows of multiple tables by doing a union and group by. So select table_name, count(*) from table_1 union table_2 group by 1. Although I've just realised I need to make the schemas match up anyway, so I might as well manually write the table name in a select. But hey, I might need it in future — user147529
if you're typing it by hand, you can just type the table name. If you generate the query you can also just use the table name as a literal string. You cannot select meta data from the table itself (AFAIK) — HoneyBadger
Ok thank you. I wanted a way to not have to write the table name to save typing — user147529

Martin Weitzmann Martin Weitzmann · Accepted Answer · 2020-08-03T12:16:12

I f you want rows and table names you can use meta tables __TABLES__:

SELECT 
  project_id, 
  dataset_id, 
  table_id, 
  row_count 
FROM 
  `bigquery-public-data.covid19_geotab_mobility_impact.__TABLES__` 
LIMIT 
  1000

Works with every dataset. These tables might get replaced with information schema tables in the future though https://cloud.google.com/bigquery/docs/information-schema-intro

Select table name as a column in BigQuery

1 Answers