Count of all individual tables in a schema

Question

I have a schema which has more than 100 tables. Is there a way in SQL server to find the count of all individual tables without entering the table names 100 times.

The result set should display something like below:

Table name	count
Table 1	Count
Table 2	Count

The only solution which I know is to list the table name in Excel and then using formula to list all the count queries and paste in SQL server. But this requires finding the list of tables for each schema and listing them. Is there there any way to automate it where the only input required will be schema name

Does this answer your question? [How to fetch the row count for all tables in a SQL SERVER database](https://stackoverflow.com/a/2836803/2029983) (Link to answer not question is intentional.) — Thom A, Aug 18 '23 at 16:06
Does this answer your question? [How to fetch the row count for all tables in a SQL SERVER database](https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database) — Paul Maxwell, Aug 20 '23 at 02:02
select object_name(object_id), rows from sys.partitions where index_id < 2; -- It's not super exact but good enough for a quick script. Index_id < 2 is either heap or clustered index. Otherwise, you have to generate a union of all tables dynamically — siggemannen, Aug 20 '23 at 17:25

score 0 · Answer 1 · answered Aug 22 '23 at 14:23

In the same way as described in this answer, but with an additional join and where clause statement to filter for whatever Schema you're interested in:

select
    o.[name] as tablename,
    ddps.row_count as [rowcount]
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
join sys.dm_db_partition_stats ddps on i.object_id = ddps.object_id and i.index_id = ddps.index_id
join sys.schemas s on o.schema_id = s.schema_id
where i.index_id < 2
and o.is_ms_shipped = 0
and s.[name] = '[YOUR SCHEMA NAME HERE]'
order by o.[name]

Count of all individual tables in a schema

1 Answers1