Row Counts of All Tables in a Database & Execute Results of Query

Teradata Studio
Fan

Row Counts of All Tables in a Database & Execute Results of Query

I am trying to create a query that will count all of the rows of all of the tables in a database.  Then I need to take those results and execute them without having to copy and paste them into a different window.

This is what I have,  but it is not working...

DECLARE commands CURSOR FOR

SELECT
T1.QRY || CASE WHEN chk <> 1 THEN ' union all' ELSE ';' END
FROM(
SELECT
'select cast(' || '''' || TRIM(databasename) || '''' || ' as varchar(1000)) ' ||  ',' || 'cast(' || '''' || TRIM(tablename) || '''' || ' as varchar(1000))' || ', CAST(COUNT(*) AS DECIMAL(32,0)) FROM ' || TRIM(databasename) || '.' || TRIM(tablename)  AS QRY
,  SUM(1) OVER(ORDER BY qry ROWS UNBOUNDED PRECEDING) AS chk
FROM dbc.tables
WHERE
databasename IN ('**bleep**')
AND tablekind = 'T'
) T1
ORDER BY chk DESC
;

DECLARE @cmd varchar(max)

open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
  exec(@cmd)
  fetch next from commands into @cmd
end

close commands
deallocate commands

I would really appreciate some help to make this work!  Thanks!


Accepted Solutions
rjg
Supporter

Re: Row Counts of All Tables in a Database & Execute Results of Query

SyntaxEditor Code Snippet

.export file rowcount.txt

SELECT ...;

.if errorcode <> 0 then .quit 4;.export reset
.set quiet off

.run file rowcount.txt


Rglass
1 ACCEPTED SOLUTION
3 REPLIES
rjg
Supporter

Re: Row Counts of All Tables in a Database & Execute Results of Query

keschm,

since you don't mention a requirement to use a stored procedure,

I will suggest you use Bteq.

You can export the results of your sql that creates the  sel  count(*) statements

and then use .run command to execute the file in one job.

 

Rglass

Fan

Re: Row Counts of All Tables in a Database & Execute Results of Query

I am familiar with the exporting part, but I am unsure about the .run command.

Would you mind giving an example?  Thanks!

rjg
Supporter

Re: Row Counts of All Tables in a Database & Execute Results of Query

SyntaxEditor Code Snippet

.export file rowcount.txt

SELECT ...;

.if errorcode <> 0 then .quit 4;.export reset
.set quiet off

.run file rowcount.txt


Rglass