SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Install SQL Server 2019
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

SQL RowCount using SQL Server System View sys.partitions

There may be different ways of SQL rowcount in a database table.
But it is very fast to use sql count rows in a table using SQL Server system view (sys.partitions).
This SQL tutorial will demonstrate sql developers how they can find total sql rowcount in a sql table using sys.partitions SQL Server system view.

In the below sql query, if you comment the line where sql table name is given as a criteria in WHERE clause, t-sql developers can get a list of all tables in the sql database and corresponding row count in that sql table.
Please note that the index id 0 is used for Heap, index id 1 is used for clustered index.
And only one of these two sql index types are used in the sum of sql row count.

  OBJECT_NAME(object_id) AS [Table Name],
  SUM(rows) AS [SQL RowCount]
FROM sys.partitions
  index_id IN (0, 1) -- 0:Heap, 1:Clustered
  AND object_id = OBJECT_ID('Sales.Store')

This method is an alternative way of SELECT COUNT(*) FROM [Table Name] for most common SQL Server rowcount method.
There is an implementation of sp_MSForEachTable to display row count of all tables here for sql developers.

We can create user defined function, so that tsql developers can use this sql function to return the records count of a specific database table easily.

CREATE FUNCTION udf_TableRowsCount(
  @tablename sysname
) RETURNS bigint


DECLARE @count bigint

  @count = SUM(rows)
FROM sys.partitions
  index_id IN (0, 1) -- 0:Heap, 1:Clustered
  AND object_id = OBJECT_ID(@tablename)

RETURN @count


And after you create sql function on your database, you can call sql function udf_TableRowsCount as follows :

SELECT [row count] = dbo.udf_TableRowsCount('Sales.Store')
SELECT [row count] = dbo.udf_TableRowsCount('Production.Product')

Please note that for the above t-sql function example, the SQL Server 2008 R2 sample database AdventureWorks is used. You can download sample database following the link.

The output of the above sql function displaying sql rowcount of the two database tables can be seen in the below screenshot.


Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems