Site Sections
Posts
255
Comments
466
Trackbacks
17
<< Notes From Computers 2K7 Radio Show! (September 16th, 2007) | Home | ASP.NET 2.0 Your Visual Blueprint by Chris Love >>
How to List All Tables and Their Rowcount in SQL Server

Just wanted to pass along my thanks to fellow Microsoft MVP Brian Knight for his Quickly Retrieving the Row Count for All Tables blog post.  I needed to list all the tables in a database and their rowcount. He posted code that does exactly that. Thanks Brian!

Have a day. :-|

posted on Saturday, September 22, 2007 12:10 PM Print
Comments
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
Tomsan
11/25/2007 9:45 AM
Really Innovative, Great Job
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
kevin kirksey
1/31/2008 1:46 PM
Thank you in advance
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
Wim
7/30/2008 8:46 AM
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2

This is the fastest way.. because you don't count them, you just look in sql where it's alreay stored
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
Wim
7/30/2008 8:50 AM
Doing a rowcount for each table will take forever where using sys.tables and sys.indexes will take milliseconds.
I have databases with over 200tables with many millions of records and I get the rwocounts instantly.

I recommend putting the query in a view, this you can just use: select * from [Views].[vw_ShowAllTableRowCounts]

create view [Views].[vw_ShowAllTableRowCounts]
as
select '['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']' AS [fulltable_name],
SCHEMA_NAME(t.[SCHEMA_ID]) as [schema_name],t.NAME as [table_name] ,i.rows
from sys.tables t
INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)

(sql 2005 code)
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
Venkatesan Prabu
9/16/2008 3:58 AM
I have written a short article regarding this.

Please check my blog and provide me your valuable feedback.

http://venkattechnicalblog.blogspot.com/2008/09/list-out-all-tables-and-row-count-in.html
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
abhay dubey
10/8/2008 11:14 AM
i want to Know about all table show in sql server in single query
Gravatar
# re: How to List All Tables and Their Rowcount in SQL Server
Mark Anderson
2/16/2009 12:39 PM
Great post! This is EXACTLY what I needed.

Mark Anderson i3solutoins inc.
mark.anderson@i3solutions.com

We specialize in Business Intelligence solutions with the Microsoft BI Stack
Comments have been closed on this topic.

Archives
Post Categories
Blogroll
DotNetNuke
My Company
Publications I Write For