How to view the number of rows in all tables in SQL Server?

To view the number of rows in all tables within a SQL Server database, you can use the following query:

SELECT 
    t.NAME AS TableName,
    SUM(p.rows) AS RowCounts
FROM 
    sys.tables t
INNER JOIN 
    sys.partitions p ON t.object_id = p.object_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.NAME
ORDER BY 
    RowCounts DESC;

This query will return the names of each table in the database, as well as the number of rows. Please note that this query will only return user-created tables, system tables will be excluded.