6

I'm trying to familiarize myself with a large database and search for relevant information among the many tables. I often find myself calling up a table, to see if there is relevant data inside, only to find that the table has no records.

How to quickly call up a list of all tables and the number of records contained therein? I'm using sql server 2008.

Thanks!

Related Question: How do I QUICKLY check many sql database tables and views to see if they are not empty or contain records

Community
  • 1
  • 1
Charles M
  • 317
  • 1
  • 3
  • 8
  • 1
    Check out this question: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database. – Gordon Linoff Mar 21 '14 at 02:20
  • Thanks Gordon. Looks like the sys.partitions table is the one I'm looking for. – Charles M Mar 21 '14 at 02:31
  • 2
    The solution I was looking for was: SELECT t.name, p.rows FROM sys.table t JOIN sys.partitions p ON t.object_id = p.object_id – Charles M Mar 21 '14 at 03:47
  • I think that you should put this as an answer than a comment because I was also looking for the same thing and there are chances that people might skip the comment. Cheers and thank you! – Aalok Apr 05 '18 at 23:47

2 Answers2

26

Right click on database -> Reports -> Standard Reports -> Disk usage by Top Tables

enter image description here

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
10

If you want to use a query, you can use this (note: it's using an undocumented stored procedure sp_msforeachtable):

create table #tempcount (tablename nvarchar(128), record_count bigint)
EXEC sp_msforeachtable 'insert #tempcount select ''?'', count(*) from ? with (nolock)'
select * from #tempcount
drop table #tempcount
Szymon
  • 42,577
  • 16
  • 96
  • 114