Technical Article

Get record count for a specific database

,

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)

You rated this post out of 5. Change rating

Share

Share

Rate

4.48 (33)

You rated this post out of 5. Change rating