473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

getting table counts

I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.

Thanks,
RickN
Jul 20 '05 #1
4 10096
CJ
Assuming your statistics are up to date you can use

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<table_name>') AND indid < 2

This will perform better than

SELECT COUNT(*) from <table_name>

This info is from http://www.sql-server-performance.com/

You could use a cursor to loop through the list of tables and stuff the
counts into a temp table. Perhaps someone else will have a way to do this
without a cursor.

Hope this helps,

CJ

"Rick" <ri**@abasoftware.com> wrote in message
news:28**************************@posting.google.c om...
I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.

Thanks,
RickN

Jul 20 '05 #2
So this would be the non-cursor solution:

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid = 0

Shervin

"CJ" <ch***@hrn.org> wrote in message news:bk**********@reader2.nmix.net...
Assuming your statistics are up to date you can use

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<table_name>') AND indid < 2

This will perform better than

SELECT COUNT(*) from <table_name>

This info is from http://www.sql-server-performance.com/

You could use a cursor to loop through the list of tables and stuff the
counts into a temp table. Perhaps someone else will have a way to do this
without a cursor.

Hope this helps,

CJ

"Rick" <ri**@abasoftware.com> wrote in message
news:28**************************@posting.google.c om...
I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.

Thanks,
RickN


Jul 20 '05 #3
Rick (ri**@abasoftware.com) writes:
I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.


SELECT 'SELECT ''name'', COUNT(*) FROM ' + name
FROM sysobjects
WHERE xtype = 'U'
AND objectproperty(id, 'IsMSShipped') = 1
ORDER BY name

Cut and paste.

If you want to run it unattended, you can use the stored procedure
sp_MSforeachtable:

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

Note that this procedure is undocumetned.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

Thanks everyone for the good ideas.
I've implemented the following and it gets me exactly what I need.

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid < 2 and o.xtype = 'u'

RickN
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: John Hunter | last post by:
I have a largish data set (1000 observations x 100 floating point variables), and some of the of the data are missing. I want to try a variety of clustering, neural network, etc, algorithms on the...
0
by: Mike Morton | last post by:
Runnint Mysql 3.23.47 I have the following two tables: backorder_notification ->product_code varchar ->email varchar ->date_added date Products ->code varchar ->name varchar
3
by: Sandros | last post by:
Background: I'm collecting usability statistics for a group of applications. Each count has the following attributes: date, application, major heading, minor heading, count. My intent is to pull...
2
by: JimJim | last post by:
Wondering if someone here could help me out, I've tried a number of ways to do this, and while I have managed to get it to work, Im SURE im not doing the optimal way, and it seems to be causing...
2
by: chudson007 | last post by:
I need help flagging duplicate records in ome tables I have. For example if I have Table1 which conatins Field1, Field2 and Field3 like below Field1 Field2 Field3 Field4 Paul 18 Null ...
7
by: No bother | last post by:
I have a table which has, among other fields, a date field. I want to get a count of records where certain criteria are met for, say, three days in a row. For example: NumWidgets Date...
10
by: jonathan184 | last post by:
Hi I tried getting this to work through dreamweaver but it did not. So i found a n example on the internet , i followed everything exactly the search script does not work. Could somebody help me...
2
by: kriz4321 | last post by:
Hi I have a array in which I need to count the number of ocurrence of a particular word for eg I need to count no of times a word "test" , "test2" occurs in a array @list. (The contents of the...
3
by: staja84f | last post by:
I'm trying to locate duplicate data in a table using 4 columns: employee_id (primary key), employeeid, lastname and firstname. I can pull up the duplicate data with the last three listed columns,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.