Is There A Script To List Tables & Number Of Records In Each

Question

Is There A Script To List Tables & Number Of Records In Each

EdA ROC

SSCrazy

Points: 2734
More actions
January 29, 2010 at 7:01 am

#218610

Our SQL database is 3rd party ERP application which deals with multiple industries and not all the tables are used. I'm "poke and hope" learning the database - we create our own Access reports and ad hoc queries when the ERP does not provide a suitable report. I'd like to run a statement to list the tables in the database and count(*). Then, in my (Excel) Data Dictionary I can note which tables don't have records and can ignore them when I'm researching.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

the recommended way is to use the count of the rows in the indexes to get the number of rows; it's already materialized, and much faster than querying each table for the number of rows woth a count(*) statement:

--for SQL 2000

SELECT sysobjects.name, MAX(sysindexes.rows) AS NUMROWS

FROM sysobjects

INNER JOIN sysindexes ON sysobjects.id = sysindexes.id

WHERE sysobjects.xtype = 'U'

GROUP by sysobjects.name

ORDER BY NUMROWS DESC,sysobjects.name

----------------------

--for 2005/2008:

Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id

From sys.partitions p

Inner Join sys.sysobjects o

on p.object_id = o.id

Where index_id in (0,1)

And o.type = 'U'

Group By object_id,index_id

Order By NumRows Desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

EdA ROC SSCrazy Points: 2734 More actions · Answer 2

Wow. 🙂

I'm sitting here smiling - that was fast, easy, and exactly what I needed.

I'm slowly learning SQL Admin tasks - it's nice that I don't have to understand how MS SQL Server works to do my job. It's up and running, a reboot has fixed any problems we've encountered. But, I am slowly learning admin stuff to do a better job.

I'll have to remember next time to include the server version, however, your covering different versions makes this post beneficial to anyone. In a couple months we are upgrading our server and going from 2000 to 2005.

Thanks for the SQL statement! Really appreciate it.

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

As a bit of a side bar, the number of rows from the system tables can go a bit astray at times. If you want them to be as accurate as possible, use DBCC UPDATEUSAGE. Before you use it, you might want to take a look at what it does in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

EdA ROC SSCrazy Points: 2734 More actions · Answer 4

Add 2 more things I've learned... DBCC UPDATEUSAGE and about Books Online (mentioned in other replies/topics), this time I search for it, found it, found the article and read it.

Thanks for the help. I appreciate it.

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

EdA ROC SSCrazy Points: 2734 More actions · Answer 5

Question about DBCC UPDATEUSAGE and regular database maintenance...

Our ERP Vendor set up SQL Server and maintenance plan. (I know the tasks being performed and the gist of the importance simply by the key words). One of the jobs is Optimization which includes rebuilding the Indexes for the tables. Would this task correct record count?

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

Ranjay SSC Rookie Points: 47 More actions · Answer 6

Ranjay

SSC Rookie

Points: 47

April 26, 2010 at 6:04 pm

#1157947

Thanks!! It saved my lot of time.:-)