CODEWITHSUNDEEP
sqlsql-servert-sql
Gita
Gita

Reputation: 159

Return number of rows from tables given the schema

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

Upvotes: 0

Views: 38

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

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;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Related Questions