Thursday, February 25, 2010

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

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