how to get the count of all the tables in a database

Question

how to get the count of all the tables in a database

tripri

SSCommitted

Points: 1584
More actions
September 2, 2010 at 11:56 am

#239813

HI,
How can i get the row count of all the tables in a db
Here is the example
i got a DB with three tables A,B,C
Table A contains 20 rows
Table B contains 30 rows
Table C contains 25 rows
Now can i write a query which gets the result like
Table---rowcount
A ------20
B ------30
C ------25
i got the list of tables using select * from sys.tables
But i got stuck from here..
Thank You...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You'd either need to use dynamic SQL, or a CLR function. Dynamic SQL will be easier and won't require opening up security rules in the database.

Would look something like this:

create table #T (

TName sysname,

Rows int);

declare Tbls cursor local fast_forward for

select name

from sys.tables;

open Tbls;

declare @Tbl sysname, @Cmd nvarchar(1000);

fetch next from Tbls into @Tbl;

while @@fetch_status = 0

begin

select @Cmd = 'select ''' + @Tbl + ''', count(*) from ' + @Tbl;

insert into #T (TName, Rows)

exec(@Cmd);

fetch next from Tbls into @Tbl;

end;

select * from #T;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95460 More actions · Answer 2

SELECT st.name, sip.rows

FROM sys.system_internals_partitions sip

JOIN sys.tables st ON st.object_id = sip.object_id

ORDER BY st.name

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

you can get counts with this:

SELECT QUOTENAME(SCHEMA_NAME(t.schema_id), ']') + '.' + QUOTENAME(t.name, ']') AS fulltable_name

, SCHEMA_NAME(t.schema_id) AS schema_name

, t.name AS table_name

, i.rows

FROM sys.tables AS t

INNER JOIN sys.sysindexes AS i

ON t.object_id = i.id

AND i.indid < 2

EDIT: Wayne's solution is better, it is using the latest tables.

-- Cory