0

I want to return the number of records present in the individual tables from the database given the scheme in TSQL

Example: Schema=ABC, Tables = ABC.T1, ABC.T2, ABC.T3

Output of the query:

No of records  Table
 10             T1
 5              T2
 36             T3
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Gita
  • 149
  • 1
  • 9

2 Answers2

2

If you don't need a transactionally consistent row count, you could query the DMVs. This will be much faster than SELECT COUNT(*) for large tables:

SELECT t.name, SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN(0,1)
WHERE OBJECT_SCHEMA_NAME(t.object_id) = N'ABC'
GROUP BY t.name;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
1

Use union all :

select count(*) as [# of records], 't1' as [Table]
from abc.t1
union all
select count(*), 't2'
from abc.t2
union all
select count(*), 't3'
from abc.t3;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • If I know only the Schema and do not know the number of tables that belong to that schema. In the example I specified 3 but what if its n? – Gita Aug 21 '18 at 11:26
  • @Gita. . . This would reflect your actual requirement --> https://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database – Yogesh Sharma Aug 21 '18 at 11:31
  • Thanks a ton! Got the solution – Gita Aug 21 '18 at 11:53