I need a row count for every table in a specified database and schema

Question

I often need to see what tables are populated within a given database and or schema. I need a query that joins the sys tables accurately to enable the where statement of the database and or schema name to be specified.

Answer 1

To see which tables are populated within a given database and schema, you can query the system catalog views in SQL Server. You can use the sys.tables, sys.schemas, and sys.partitions system views to get this information. Here's a query that will help you determine which tables have rows in them within a specified database and schema:

Query

USE YourDatabaseName; -- Replace with your actual database name

GO

SELECT 

    sch.name AS SchemaName,

    tbl.name AS TableName,

    SUM(part.rows) AS RowCount

FROM 

    sys.tables tbl

    INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id

    INNER JOIN sys.partitions part ON tbl.object_id = part.object_id

WHERE 

    part.index_id IN (0, 1) -- 0 for heaps, 1 for clustered indexes (i.e., the table itself)

    AND sch.name = 'YourSchemaName' -- Replace with your actual schema name

GROUP BY 

    sch.name,

    tbl.name

HAVING 

    SUM(part.rows) > 0 -- Only include tables with rows

ORDER BY 

    sch.name,

    tbl.name;

Explanation

• USE YourDatabaseName: This changes the context to the database you want to query.
• sys.tables: Contains a row for each table object.
• sys.schemas: Contains a row for each schema.
• sys.partitions: Contains a row for each partition of a table or index.
• INNER JOIN: Joins the sys.tables, sys.schemas, and sys.partitions views.
• part.index_id IN (0, 1): Filters to include only rows representing the table itself (heaps and clustered indexes).
• sch.name = 'YourSchemaName': Filters the results to a specific schema.
• GROUP BY: Groups the results by schema and table name.
• HAVING SUM(part.rows) > 0: Filters to include only tables that have rows.

Example

If you want to check tables in the Sales schema within the AdventureWorks2019 database, you would replace YourDatabaseName with AdventureWorks2019 and YourSchemaName with Sales:

USE AdventureWorks2019;

GO

SELECT 

    sch.name AS SchemaName,

    tbl.name AS TableName,

    SUM(part.rows) AS RowCount

FROM 

    sys.tables tbl

    INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id

    INNER JOIN sys.partitions part ON tbl.object_id = part.object_id

WHERE 

    part.index_id IN (0, 1)

    AND sch.name = 'Sales'

GROUP BY 

    sch.name,

    tbl.name

HAVING 

    SUM(part.rows) > 0

ORDER BY 

    sch.name,

    tbl.name;

This query will return a list of tables in the Sales schema that have rows, along with the count of rows in each table. Adjust the database and schema names as needed for your specific use case.