You are exploring a database in SQL Server using SSMS. You need a script that simply reports the list of table names and the number of rows in each table. You need some metadata. What would the script look like? Here is a post over at Stack Overflow called How to fetch the row count for all tables in a SQL SERVER database. sp_MSForEachTable is an undocumented stored procedure. It is strongly recommended to avoid using undocumented features of SQL Server in a poduction environment. Do this only in a non-production environment.

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
SELECT SUM(row_count) AS total_row_count FROM #counts
DROP TABLE #counts

Note that the pound (#) sign before the table name indicates that the table counts is a temporary table. Below is a screenshot in SSMS of the above query that was run in the sample Chinook database. The Chinook database represents a fictitious digital media store, including tables for artists, albums, media tracks, invoices and customers.