Select count to all tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select count to all tables

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2011-01-31 : 01:56:27

Hi,

I've database named ST3DB. In ST3DB, having a tables as following
1. t1
2. t2
3. t3
4. t9
5. t23

I need to count many record on each table. As a result, the output as following

Tables | Record No
------------------------
t1 | 34
t2 | 78
t3 | 77
t9 | 84
t23 | 278

How my sql looks like?

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-31 : 02:08:01

Hi,
try this!

select o.name,s.row_count from sys.objects o
join sys.dm_db_partition_stats s on o.object_id=s.object_id
AND o.is_ms_shipped = 0

sathish

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-01-31 : 03:58:46

also

--Count Records in All Tables
SELECT
sysobjects.Name
, sysindexes.Rows
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
type = 'U'
AND sysindexes.IndId < 2

IRK

wkm1925
Posting Yak Master

207 Posts

Posted - 2011-02-01 : 20:38:01

tq sir

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-01 : 23:41:44

welcome

IRK

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-02-03 : 09:24:08

sys.partitions system view can also be used to list all tables in a sql database with row counts. Here is an example [url]http://www.kodyaz.com/articles/sql-rowcount-using-sql-server-system-view-sys-partitions.aspx[/url]

-------------
Eralper
http://www.kodyaz.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources