Hi,
My database is quite big right now, I want to check how many records of every table in my database.
Are there any ways to check how big my data in every tables without checking one by one of the tables?
thx,
Joe
Try this:
SELECT OBJECT_NAME(object_id) AS table_name, MAX(rows) AS row_count
FROM sys.partitions
GROUP BY object_id
ORDER BY table_name
Or, if you also need the schema name:
SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(object_id, 'SCHEMAID') AS int)) AS schema_name,
OBJECT_NAME(object_id) AS table_name, MAX(rows) AS row_count
FROM sys.partitions
GROUP BY object_id
ORDER BY schema_name, table_name
1 Like
Answer to a slightly different question, but this script will let you sort by Rows-per-Table, size-of-table, and all sorts of other "size" related factors
1 Like
For those that are lazy and just want to see the results - you can right-click the database, select Reports, Standard Reports and the Disk Usage by Table report.
Surprisingly bad those reports, aren't they?
All the info you (most likely) need ... but ...
Sort by Column provides Up/Down arrow, but whichever you press you get Ascending - and then, on a second click, Descending
But if you do that you have the original row-highlight columns, but no longer on alternative lines
And if you want to just Copy & Paste a value - e.g. to rebuild a table's indexes - ... you can't 
ok thx
Not the best - not at all...but still better than nothing and good enough for a quick look. You can also add columns in SSMS to display number of rows, index usage and data usage...I add those columns to the display so I can always check the size and number of rows at a glance.
I do have my own script - which is what I run when I need to share the data or perform more analysis.
Yup, that's what I do too. I stumbled over the SSMS report recently, by chance, and was surprised I could Copy and Paste from it ... as the first thing I wanted to do was to rebuild one of the tables!