SQL Table Rows

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
IkramfiesAsked:
Who is Participating?
 
James MurrellProduct SpecialistCommented:
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
 
reb73Commented:
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
 
BrandonGalderisiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.