Total no of rows in all tables

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

General SQL Server Forums

New to SQL Server Programming

Total no of rows in all tables

Author

Topic

kneel
Starting Member

36 Posts

Posted - 2008-02-26 : 09:06:00

Hi,

Does anyone know how to calcuate total no of rows of all tables from database in single query?

Thanks in advance

--kneel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 09:10:45

Yes.

E 12°55'05.25"
N 56°04'39.16"

kneel
Starting Member

36 Posts

Posted - 2008-02-26 : 09:12:48

can u please help me ?

Thanks in advance

--kneel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 09:19:27

[code]CREATE TABLE #Temp (TableName VARCHAR(400), Records INT);

INSERT #Temp
EXEC sp_msforeachtable 'SELECT ''?'' AS TableName, COUNT(*) AS Records FROM ?'

SELECT * FROM #Temp

DROP TABLE #Temp[/code]

E 12°55'05.25"
N 56°04'39.16"

kneel
Starting Member

36 Posts

Posted - 2008-02-26 : 09:23:36

Thanks a lot .....

--kneel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 09:32:07

DBCC UPDATEUSAGE('DBNAME') WITH COUNT_ROWS

select object_name(id) as table_name,rows from sysindexes
where indid<2
order by 1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 09:46:23

Does this work if there is no index in a table?

E 12°55'05.25"
N 56°04'39.16"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 09:51:48

Peso,

I liked you script.

CREATE TABLE #Temp (TableName VARCHAR(400), Records INT);

INSERT #Temp
EXEC sp_msforeachtable 'SELECT ''?'' AS TableName, COUNT(*) AS Records FROM ?'

SELECT * FROM #Temp

DROP TABLE #Temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 11:00:37

quote:
Originally posted by Peso

Does this work if there is no index in a table?

E 12°55'05.25"
N 56°04'39.16"

YES

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 11:02:29

quote:
Originally posted by sodeep

Peso,

I liked you script.

CREATE TABLE #Temp (TableName VARCHAR(400), Records INT);

INSERT #Temp
EXEC sp_msforeachtable 'SELECT ''?'' AS TableName, COUNT(*) AS Records FROM ?'

SELECT * FROM #Temp

DROP TABLE #Temp

Note that sp_msforeachtable is undocumented

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-26 : 11:47:46

If you are using SQL Server 2005, look at this script:
How many rows per table?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97898

You can also use this script with SQL 7, 2000, and 2005 for a more detail analysis of space used:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-26 : 11:55:04

quote:
Originally posted by madhivanan

DBCC UPDATEUSAGE('DBNAME') WITH COUNT_ROWS

select object_name(id) as table_name,rows from sysindexes
where indid<2
order by 1

Madhivanan

Failing to plan is Planning to fail

I have seen cases where sysindex tab doesn't have the correct count.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources