Thursday, February 25, 2010

Number of different way to get total no of row from tables

Shout it kick it on DotNetKicks.com
Following are the different ways to get the number of records in a table:


First
List all table of the database with row counts.

SELECT Table_Name = object_name(object_id), Total_Rows = SUM(st.row_count) FROM sys.dm_db_partition_stats st GROUP BY object_name(object_id) HAVING SUM(st.row_count) <> 0 ORDER BY object_name(object_id)





Second
List table with the no of row in table.

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id] WHERE sysobjects.xtype = 'U' and sysobjects.[name]='App3_Employee' GROUP BY sysobjects.[name] ORDER BY max(rows) DESC GO





Third
List table with row count.

SELECT Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'app3_employee'





Fourth
List rows with the row count.

SELECT PKEY, FIRSTNAME, LASTNAME, ROW_NUMBER() OVER (ORDER BY PKEY) AS Position, COUNT(*) OVER () AS TotalRows FROM app_employee

1 comment:

  1. Nice article; just wondering if you could outline some benefits/drawbacks for each method. Thanks!

    ReplyDelete