<< Windows: Network folders do not refresh automatically in Windows Explorer | Home | SQL Server: Which tables are storing the most physical data? >>
SQL Server: Retrieving the number of rows in all tables in a database

Overview

I'm in the process of reviewing a database for performance analysis.  One of the most important tasks to perform is reviewing of statistics.  Because this database is currently live, I cannot get the locks to use the sys.dm_db_index_physical_stats built-in function.  I have an Oracle background and didn't know how to get the row count quickly.  Here's my solution...

References

Solution

I use the following code to get the total number of rows for all tables in the current database:

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows,
  CAST(
    CASE max(sysindexes.[rows])
      WHEN 0 THEN -0 
      ELSE LOG10(max(sysindexes.[rows])) 
    END
    AS NUMERIC(5,2))
  AS L10_TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC
GO

Versions

Metadata


Bookmark with :
Digg It! DZone StumbleUpon Technorati Reddit Del.icio.us Newsvine Furl Blinklist
posted @ Wednesday, January 09, 2008 12:01 PM | in SQL Server Software Development IT Management

Comments

Gravatar
# re: SQL Server: Retrieving the number of rows in all tables in a database
Posted by ran
on 3/18/2008 10:22 AM
really good ,it helps me alot
Gravatar
# re: SQL Server: Retrieving the number of rows in all tables in a database
Posted by Muhammad Azim
on 4/26/2008 10:17 AM
very nice work.
Gravatar
# re: SQL Server: Retrieving the number of rows in all tables in a database
Posted by Dave
on 4/28/2008 12:51 PM
Do you know of a similar trick for oracle too by any chance?
Gravatar
# re: SQL Server: Retrieving the number of rows in all tables in a database
Posted by Stephen Horsfield
on 4/28/2008 12:59 PM
It is quite a long time since I worked with Oracle. However, the row statistics were available in the management tools. If you need a query-based solution, I'm afraid I can't help.

Sorry.
Gravatar
# re: SQL Server: Retrieving the number of rows in all tables in a database
Posted by Mahesh Acharya
on 10/17/2008 1:00 PM
Hey Dave! for oracle, why don't you use the following code.
----------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE zzz_tblCount PURGE'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/
--------------------------------------------------------------------------------
CREATE TABLE zzz_tblCount
(
TBLNAME VARCHAR2(30),
CROWS VARCHAR2(10)
);
-------------------------------------
DECLARE
CURSOR MON_CURSOR IS SELECT TNAME TABLENAME FROM TAB WHERE TNAME NOT LIKE '%BIN%';
BEGIN
FOR a IN MON_CURSOR LOOP

BEGIN
EXECUTE IMMEDIATE
'INSERT INTO /*+ parallel(A,10) */ zzz_tblCount A
(
TBLNAME,
CROWS
)
SELECT
'''||a.TABLENAME||''',
count(*)

FROM
'|| a.TABLENAME||' a';
END;
END LOOP;
END;
/
--------
select * from zzz_tblCount;

Post Comment

Title *
Name *
Email
Url
Comment *  


Please add 5 and 7 and type the answer here: