如何为数据库中的每个表返回（行计数，表名）的查询？

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

使用ANSI sql,如何为数据库中的每个表返回(行计数,表名)的查询?

Thanks Viki

3 个解决方案

#1

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:

在“ANSI”(ISO)SQL中,您必须转到“信息架构”视图才能获取信息。正式地说,这是ISO / IEC 9075-11:2003(或2008)。另请参阅有关SQL的Wikipedia文章,其中说:

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.

SQL / Schemata,或信息和定义模式,部分由ISO / IEC 9075,第11部分定义.SQL / Schemata定义信息模式和定义模式,提供一组通用工具,使SQL数据库和对象能够自我描述。这些工具包括SQL对象标识符,结构和完整性约束,安全和授权规范,ISO / IEC 9075的功能和包,基于SQL的DBMS实现提供的功能支持,基于SQL的DBMS实现信息和大小调整项,以及DBMS实现支持的值。标准的这一部分包含强制和可选功能。

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

采取一些盐的“强制性功能” - 我怀疑大多数DBMS甚至没有实现强制性部分。

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

否则,答案是特定于DBMS的。这些信息是可用的 - 详细信息从DBMS到DBMS不等。

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.

SQL / Schemata(2003版)的第5.61节定义了“TABLES View”。它似乎不包括行计数,也没有我能看到的任何其他视图。所以,Beau Simensen的评论似乎是正确的;没有简单的方法可以便携地找到行计数。

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.

因此,我认为没有可移植的方法来确定行计数和表名。

#2

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.

最好的方法是编写一个从数据库元数据中提取信息的过程(Oracle - all_tables,MySql - information_schema.tables)并运行计数查询。

#3

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):

例如,对于SQL Server,您可以使用此查询进入具有所有系统视图的“sys”模式(SQL Server 2005及更高版本):

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.......

但这当然不适用于Oracle,MySQL,PostgreSQL或IBM DB2 .......

Marc

#1