SQL Script: List all tables in DB with their row counts

Often I need to test something and use SQL database table of particular size for my test, and sometimes it is easier to pick up some table of appropriate size (at least in terms of rows number) from what you already have. This is the case when this SQL script may come in handy:

DROP TABLE #counts

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 row_count DESC

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *