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 4 10096
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
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
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
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |