How to to know the records of each table in a DB? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to to know the records of each table in a DB?

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-08-02 : 18:04:10

Is there a way to know the records [count (*)] of each table that exist in a specific database.

What if a database holds 200 tables, I dont want to write the select statment (select count(*) from table) 200 times to get the count of all the tables in a particular database.

Does master or any other table hold this information.

What is the best way to get this information?.

Looking for a quick response.

Thanks a million....

Zee

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 18:25:14

If all the tables have primarykey's you can query sysindexes and check rows column to get that info.

Here's a sample:


SELECT 
    [TableName] = so.name, 
    [Table id]=so.id,
    [RowCount] = MAX(si.rows) 
 FROM 
sysobjects so, sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND   si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name,so.id 
ORDER BY 
    2 DESC

You have to do COUNT(*) for tables that dont have PK.
There's also sp_spaceused that gets you an approximate info.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-03 : 03:28:12

Provided you run DBCC Updateusage too

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 04:11:56

Before or after the code dinakar provided?

OP might be as advanced as you or him.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-03 : 04:35:37

quote:
Originally posted by Peso

Before or after the code dinakar provided?
OP might be as advanced as you or him.

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

Before running Dinakar's code

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources