In some cases our need is to find out which table consists of the maximum number of Rows or to find out the total number of rows in the database that includes both System and User tables. To find this out using Transact-SQL, follow the below given examples.
Example: We will find outthe count of rows of tables inthe database that includes both User tables and System tables, In which RowsCounts is in descending order. We will use 'sys.dm_db_partition_stats'. There are 341 User and System tables in below given database.
Query:
- SELECT OBJECT_NAME(OBJECT_ID) TableName, sys_dm_db_partition_stats.row_count
- FROM sys.dm_db_partition_stats sys_dm_db_partition_stats
- WHERE index_id < 5
- ORDER BY sys_dm_db_partition_stats.row_count DESC
- GO
Output:
Example: Now in this example, We will find out count of rows of only User tables in database, in which RowsCounts is in descending order. We will use 'sys.partitions', 'sys.tables' and 'sys.schemas'. There are 71 User tables in my database.
Query:
- SELECT sys_schemas.name +'.'+ sys_tables.name TableName
- ,SUM(sys_partitions.rows) RowCounts
- FROM sys.tables sys_tables
- INNER JOIN sys.partitions sys_partitions
- ON sys_partitions.OBJECT_ID = sys_tables.OBJECT_ID
- INNER JOIN sys.schemas sys_schemas
- ON sys_tables.schema_id = sys_schemas.schema_id
- WHERE sys_tables.is_ms_shipped = 0
- AND sys_partitions.index_id
- IN (1,0)
- GROUP BY sys_schemas.name,sys_tables.name
- ORDER BY SUM(sys_partitions.rows) DESC
- GO
Output: