Get record count for a specific database

James Rea, 2013-10-23 (first published: 2007-12-17)

I found it interesting to sometimes know how many records there are in my databases. This coding will allow you to count all of the records contained within any single user database you have created. It is very simple to switch databases to count (1 line of coding need be modified). You can also sort the results by either the record count or the table name.

This coding is a modification of a post I had found that would display all of the tables and their record counts. I found the original posting to also include other tables that may not hold as much relevance such as system tables, etc. You can view the original posting at: http://www.sqlservercentral.com/scripts/Miscellaneous/30324/

I have attempted to make it clear as to what is the original coding and what I had added, and what additional options you could look to use with this coding.

    --Specify the name of the database to count in the following line
USE AdventureWorks; --Added by James_DBA
GO

--Code modified from original posting on SQLServerCentral.Com
--URL: http://www.sqlservercentral.com/scripts/Miscellaneous/30324/
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
--This specifies 'user' databases only
AND xtype = 'u' --Added by James_DBA
--This omits the diagrams table of the database
--You may find other system tables will need to be ommitted,
--you would just name them all here using the <> operator
--i.e. o.name <> dtproperties, o.name <> 'sysdiagrams'
AND o.name <> 'sysdiagrams' --Added by James-DBA

--You could also look further into filtering out temp tables,
--or user specified tables
ORDER BY i.rowcnt DESC --I found it more useful to display 
--the results by 'Row Count' Descending
--The original posting suggested to sort by Table name by
--using the following line, instead of the line I use above:
--ORDER BY o.name

--The following line adds up all the rowcount results and places
--the final result into a seperate column (below the first resulting table)
COMPUTE SUM(i.rowcnt); --Added by James_DBA
GO

Rate

4.48 (33)

Share

Share

Rate

4.48 (33)

Related content

Partitioned Tables and Indexes in SQL Server 2005

Table-based partitioning features in SQL Server 2005 provide flexibility and performance to simplify the creation and maintenance of partitioned tables. Trace the progression of capabilities from logically and manually partitioning tables to the latest partitioning features, and find out why, when, and how to design, implement, and maintain partitioned tables using SQL Server 2005. (41 printed pages)

Additional Articles

2005-02-07

1,652 reads

SQL Server 2005 Part 5 – High Availability and Scalability Enhancement

In this article, we continue our discussion on high availability and scalability enhancements in SQL Server 2005 Beta 2 release. Features such as clustering, database mirroring, online indexing, fast recovery, database snapshots, and snapshot isolation transaction level have already been covered earlier in this series – now we will concentrate on the remaining features, such as a new method of table and index partitioning, backup and restore improvements, and new hardware support options.

Additional Articles

2005-02-02

1,738 reads