CODEWITHSUNDEEP
sql
Viki
Viki

Reputation:

how to make query that returns (row count, tablename) for every table in the database?

Using ANSI sql, how can I make query that returns (row count, tablename) for every table in the database ?

Thanks Viki

Upvotes: 1

Views: 2178

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 754940

To the extent that it is possible in "ANSI" (ISO) SQL, you'd have to go to the Information Schema views to get the information. Officially, that's ISO/IEC 9075-11:2003 (or 2008). See also the Wikipedia article on SQL, where it says:

The SQL/Schemata, or Information and Definition Schemas, part is defined by ISO/IEC 9075, Part 11. SQL/Schemata defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier, structure and integrity constraints, security and authorization specifications, features and packages of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items, and the values supported by the DBMS implementations. This part of the standard contains both mandatory and optional features.

Take the 'mandatory features' with a pinch of salt - I suspect that most DBMS do not implement even the mandatory parts.

Otherwise, the answer is very DBMS-specific. The information is available - the details differ from DBMS to DBMS, that's all.

Section 5.61 of SQL/Schemata (2003 version) defines the "TABLES View". It does not appear to include a row count, and neither does any of the other views that I can see. So, it appears that the comment by Beau Simensen is correct; there is no simple way to find the row counts portably.

CREATE VIEW TABLES AS
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
           TABLE_TYPE, SELF_REFERENCING_COLUMN_NAME, REFERENCE_GENERATION,
           USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA,
           USER_DEFINED_TYPE_NAME, IS_INSERTABLE_INTO, IS_TYPED,
           COMMIT_ACTION
FROM DEFINITION_SCHEMA.TABLES
WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
      ( SELECT TP.TABLE_CATALOG, TP.TABLE_SCHEMA, TP.TABLE_NAME
        FROM DEFINITION_SCHEMA.TABLE_PRIVILEGES AS TP
        WHERE ( TP.GRANTEE IN ( 'PUBLIC', CURRENT_USER )
                OR GRANTEE IN ( SELECT ROLE_NAMEFROM ENABLED_ROLES ) )
        UNION
        SELECT CP.TABLE_CATALOG, CP.TABLE_SCHEMA, CP.TABLE_NAME
        FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES AS CP
        WHERE ( CP.GRANTEE IN ( 'PUBLIC', CURRENT_USER )
                OR CP.GRANTEE IN ( SELECT ROLE_NAME
                                   FROM ENABLED_ROLES ) ) )
   AND TABLE_CATALOG = ( SELECT CATALOG_NAME
                         FROM INFORMATION_SCHEMA_CATALOG_NAME );

As you can see from the select-list, the row count is not included.

I think, therefore, that there is no portable way to determine the row counts and table names.

Upvotes: 2

marc_s
marc_s

Reputation: 755321

I don't think you'll be able to find a way that work on all systems, since those "catalog views" or system views (or whatever they're called) are usually pretty vendor-specific.

For SQL Server for instance, you can use this query which reaches into the "sys" schema which has all the system views (SQL Server 2005 and up):

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
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
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id)

But that won't work on Oracle, MySQL, PostgreSQL or IBM DB2, of course.......

Marc

Upvotes: 0

Pradeep
Pradeep

Reputation: 2497

the best way is to write a procedure which pulls information from the metadata of the DB (Oracle - all_tables, MySql - information_schema.tables) and run the count query.

Upvotes: 0

Related Questions