Row Counts for each table on Database from Metadata

Im sure a lot of people do this already but for those of you who dont there is an easy way to find the row counts of all of your tables without having to wait for count(*) commands.

SQL Server saves this information for you.

Just run the below on each database you want to check OR if you want you can use the sp_foreachDB I mentioned in a previous post (Run SQL On Each Database) to run it on multiple databases. If I was to do that I would pump the data into a temp table each time and then return the results from it.

SELECT
 DB_NAME() as DatabaseName, 
        st.Name,
 sc.Name as ''Schema'',
        SUM(CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows 
            ELSE 0 END) AS Rows,
 st.Modify_Date
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sc on st.schema_id = sc.schema_id
GROUP BY st.Name, sc.Name, st.Modify_Date
ORDER BY rows desc, sc.Name, st.Name