Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Table Rows

Posted on 2009-02-15
4
Medium Priority
?
227 Views
Last Modified: 2012-06-21
Dear All,

I want to know the number of rows of each  table in my SQL database. It will be helpful if you can give me a valuable solution for this. I need the query to get the rows of each table in my database done in SQL.

Thanks and Regards
0
Comment
Question by:Ikramfies
3 Comments
 
LVL 31

Accepted Solution

by:
James Murrell earned 672 total points
ID: 23645820
i use

USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable sp_spaceused ?
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size,  KB, ) AS integer) DESC
DROP TABLE #temp

taken from http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/
0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 664 total points
ID: 23645826
A quick executing one-line script would be this, but it depends on the sysindexes table which may not be up-to-date -

SELECT OBJECT_NAME(Id), name, rowcnt from dbo.sysindexes where indid < 2 ORDER BY rowcnt DESC

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 664 total points
ID: 23646687
This procedure will give you an ACCURATE count of the records in each table.
create procedure up_GetAllTableCountsInDB
as
create table #TableCounts (TableName nvarchar(255), TableCount int)
 
insert into #TableCounts
exec sp_msforeachtable 'select ''?'',count(*) from ?'
 
select * from #TableCounts
drop table #TableCounts
 
GO

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question