SQL Example
To get all the tables of a schema, sample in this case, you could use this code:
\trim SELECT
'select '+x'27',
table_name as "table name",
x'27'+', count(*) from sample.',
table_name as tablename
FROM information_schema.tables where table_schema = 'sample'
FETCH FIRST 1 ROW ONLY;
SELECT
'union all',
'select '+x'27',
table_name as "table name",
x'27'+', count(*) from sample.',
table_name as tablename
FROM information_schema.tables where table_schema = 'sample'
OFFSET 1;
\g
You could put this in a script to enhance it further: call the script giving the SQL connectivity string to the respective warehouse and optionally the schema name. Output is in comma-separated format ready to be loaded anywhere.
#!/bin/bash
set -e
#DBCONNECT=@av-mc07gf2l3329.avstage.actiandatacloud.com,tcp_ip,27832[dbuser,password]::db
DBCONNECT=$1
SCHEMA=$2
: ${SCHEMA:=sample}
The desired generated SQL query result from the first SQL call looks like this:
select 'ontime', count(*) from sample.ontime\g
Awk appends the \g to the generated query (something not possible in SQL).
The second SQL call executes that generated query.
The first SELECT statement is optional. It is the header for the comma-separated output:
sql -S ${DBCONNECT} <<-EOF | awk '\{print}END\{print "\\g"}' | sql -S ${DBCONNECT} \trim SELECT
'select '+x'27'+' table_name'+x'27',
','+x'27'+','+x'27'+',',
x'27'+' rows'+x'27'+';';
SELECT
'select '+x'27',
table_name as "table name",
x'27'+'as table,'+x'27'+','+x'27',
', count(*) as rows from '+x'22'+'${SCHEMA}'+x'22'+'.',
table_name as tablename
FROM information_schema.tables where table_schema = '${SCHEMA}'
FETCH FIRST 1 ROW ONLY;
SELECT
'union all',
'select '+x'27',
table_name as "table name",
x'27'+'as table,'+x'27'+','+x'27',
', count(*) from '+x'22'+'${SCHEMA}'+x'22'+'.',
table_name as tablename
FROM information_schema.tables where table_schema = '${SCHEMA}'
OFFSET 1;
\g
EOF
Last modified date: 01/04/2024