Displays the row count of tables where the row count is greater than 0
SELECT
*
FROM (
SELECT
QUOTENAME(SCHEMA_NAME([System_Objects].schema_id)) + '.' + QUOTENAME([System_Objects].name) AS [Table_Name]
, SUM ([System_Partitions].Rows) AS [Row_Count]
FROM
sys.objects AS [System_Objects]
INNER JOIN sys.partitions AS [System_Partitions] ON [System_Objects].object_id = [System_Partitions].object_id
WHERE
[System_Objects].type = 'U'
AND [System_Objects].is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
[System_Objects].schema_id
, [System_Objects].name
) AS [System_Tables]
WHERE
[System_Tables].Row_Count > 0
ORDER BY
[System_Tables].Row_Count DESC
, [System_Tables].Table_Name