2

How do you query table names and row counts for all tables in a schema using HP NonStop SQL/MX?

Thanks!

Wuf_fang
  • 21
  • 4

2 Answers2

0

This might help you, althought this is more standard SQL and im not sure how much variation comes into sqlmx

SELECT 
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

Obviously this is an example, replace example data and table info with yours

Cacoon
  • 2,467
  • 6
  • 28
  • 61
  • Cacoon, thanks I saw that and it is pretty useful for SQL Server but trying to get system info using SQL/MX metadata seems to be far different and obfuscated in the reference and query guide. – Wuf_fang Jul 05 '18 at 23:10
  • Damn, apologies then; Is there any information/guide/books that talk about this sort of thing? is it just your particular situation thats problematic or doing this on any SQLMX is troublesome? – Cacoon Jul 05 '18 at 23:14
0

Here is how to list the tables in a sql/mx schema, note that the system catalog name given here is an example, replace NONSTOP_SQLMX_SYSNAME with NONSTOP_SQLMX_xxxx where xxxx is the Expand node name of your system.

Also the definition schema name includes the schema version number, this example uses 3600. This example lists all the base table names in schema JDFCAT.T.

See chapter 10 of the SQL/MX reference manual for information on the metadata tables.

The table row counts are not stored in the system metadata, so you can't get them from there. For a table do SELECT ROW COUNT FROM TABLE;

SELECT
    O.OBJECT_NAME 
FROM 
    NONSTOP_SQLMX_SYSNAME.SYSTEM_SCHEMA.CATSYS C
    INNER JOIN NONSTOP_SQLMX_SYSNAME.SYSTEM_SCHEMA.SCHEMATA S
        ON (S.CAT_UID = C.CAT_UID)
    INNER JOIN JDFCAT.DEFINITION_SCHEMA_VERSION_3600.OBJECTS O
        on S.SCHEMA_UID = o.SCHEMA_UID
    WHERE C.CAT_NAME = 'JDFCAT' AND
          S.SCHEMA_NAME = 'T' AND
          O.OBJECT_TYPE = 'BT'
    READ UNCOMMITTED ACCESS;
John Furlong
  • 1