how to know the table having maximum rows

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

how to know the table having maximum rows

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-10-31 : 02:41:46

Dear Experts,
i've one database with around 1400 tables.
is there any possibilities to know at a time what is the count(*) in each table? actually i need tables which are having maximum data.

my expected result is like this
table num_rows
table1 20000
table2 10000

like this
thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 04:15:31

dbcc updateusage

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

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-10-31 : 09:10:05

what is "indid" column madhi?

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 09:14:45

It is the index id

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-31 : 09:24:03

You can use this script.

Script to analyze table space usage:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-01 : 08:18:03

Dear Madhi,
why should be the indexID<2?
what is the reason behind that?

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 08:36:27

quote:
Originally posted by sunsanvin

Dear Madhi,
why should be the indexID<2?
what is the reason behind that?

Vinod
Even you learn 1%, Learn it with 100% confidence.

Run this and see

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

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-01 : 08:46:32

Yea...
when the value of indexid is >=2, the rows are 0.

Vinod
Even you learn 1%, Learn it with 100% confidence.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-01 : 09:03:14

> when the value of indexid is >=2, the rows are 0.

and this tells you what about indid >= 2?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-04 : 01:32:45

quote:
Originally posted by spirit1

> when the value of indexid is >=2, the rows are 0.

and this tells you what about indid >= 2?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

exactly what is going inside this spirit1?....
actually i didnt get the logic

Vinod
Even you learn 1%, Learn it with 100% confidence.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-04 : 05:32:07

open BOL = Books Online = SQL Server Help.
in the index textbox type in sysindexes
press Enter.
look what it say for the description of the indid column.
Any clearer now?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources