使用Teredata中的每个表行数选择所有表/视图名称。

I have been stuck into a question. The question is I want to get all Table name with their Row Count from Teradata.

我遇到了一个问题。问题是，我希望从Teradata获得所有的表名。

I have this query which gives me all View Name from a specific Schema.

我有这个查询，它让我从一个特定的模式中看到所有的视图名称。

I ] SELECT TableName FROM dbc.tables WHERE tablekind='V' AND databasename='SCHEMA' order by TableName;

从dbc中选择TableName。tablekind='V'和databasename='SCHEMA' order by TableName;

& I have this query which gives me row count for a specific Table/View in Schema.

我有这个查询，它为我提供了在模式中特定表/视图的行计数。

II ] SELECT COUNT(*) as RowsNum FROM SCHEMA.TABLE_NAME;

选择COUNT(*)为SCHEMA.TABLE_NAME中的RowsNum;

Now can anyone tell me what to do to get the result from Query I (TableName) and put it into QUERY II (TABLE_NAME) You help will be appreciated.

现在，任何人都可以告诉我如何从查询I (TableName)中获取结果，并将其放入查询II (TABLE_NAME)中，您将得到帮助。

Thanks in advance,

提前谢谢,

Vrinda

2 个解决方案

#1

This is a SP to collect row counts from all tables within a database, it's very basic, no error checking etc.

这是一个从数据库中的所有表收集行计数的SP，它非常基本，没有错误检查等。

It shows a cursor and dynamic SQL using dbc.SysExecSQL or EXECUTE IMMEDIATE:

它显示了使用dbc的游标和动态SQL。SysExecSQL或立即执行:

CREATE SET TABLE RowCounts
     (
      DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      RowCount BIGINT,
      COllectTimeStamp TIMESTAMP(2))
PRIMARY INDEX ( DatabaseName ,TableName )
;

REPLACE PROCEDURE GetRowCounts(IN DBName VARCHAR(30))
BEGIN
  DECLARE SqlTxt VARCHAR(500);
   FOR cur AS
      SELECT
         TRIM(DatabaseName) AS DBName,
         TRIM(TableName) AS TabName
      FROM dbc.Tables
      WHERE DatabaseName = :DBName
      AND TableKind = 'T'
   DO
     SET SqlTxt =
         'INSERT INTO RowCounts ' ||
         'SELECT ' ||
         '''' || cur.DBName || '''' || ',' ||
         '''' || cur.TabName || '''' || ',' ||
         'CAST(COUNT(*) AS BIGINT)' ||  ',' ||
         'CURRENT_TIMESTAMP(2) ' ||
         'FROM ' || cur.DBName ||
         '.' || cur.TabName || ';';

     --CALL dbc.sysexecsql(:SqlTxt);
     EXECUTE IMMEDIATE sqlTxt;
   END FOR;
END;

If you can't create a table or SP you might use a VOLATILE TABLE (as DrBailey suggested) and run the INSERTs returned by following query:

如果不能创建表或SP，可以使用VOLATILE表(如DrBailey建议的)，并运行以下查询返回的insert:

SELECT
   'INSERT INTO RowCounts ' ||
   'SELECT ' ||
   '''' || DatabaseName || '''' || ',' ||
   '''' || TableName || '''' || ',' ||
   'CAST(COUNT(*) AS BIGINT)' ||  ',' ||
   'CURRENT_TIMESTAMP(2) ' ||
   'FROM ' || DatabaseName ||
   '.' || TableName || ';'
FROM dbc.tablesV 
WHERE tablekind='V' 
AND databasename='schema' 
ORDER BY TableName;

But a routine like this might already exist on your system, you might ask you DBA. If it dosn't have to be 100% accurate this info might also be extracted from collected statistics.

但是这样的例程可能已经存在于您的系统中，您可能会问DBA。如果它不需要百分之百准确，这个信息也可以从收集的统计数据中提取出来。

#2

Use dnoeth's answer but instead use create "create volatile table" this will use your spool to create the table and will delete all data when your session is closed. You need no write access to use volatile tables.

使用dnoeth的答案，但使用create“create volatile table”，它将使用您的spool创建表，并在会话关闭时删除所有数据。您不需要写访问来使用volatile表。

#1