Get the Row Counts for all tables in Database in Sql Server 2005

One way of doing the above is using sys.partitions according to mandar in which the T-SQL can be further cleaned up to eliminate all the System tables and views as given below:

DBCC UPDATEUSAGE(AdventureWorksDW)

SELECT OBJECT_NAME(object_id) TableName,
SUM(Rows) NoOfRows --total up if there is a partition
FROM sys.partitions
WHERE index_id <>) --Restrict the Table Names
GROUP BY object_id

but the problem with the above Sql Statment is that the statistics does not get updated unless we use DCC Updateuage.

Credit goes to Dipendra Baghel, Gidwani Sunil and Prashant tiwari today who were able to write a simple T-SQL using CURSOR ( pasted below) that can take count of rows for all tables and export to a table in a database . Astonishing that atlast I found the same code already written by Madhu K Nair.

Drop table #TEMP

DECLARE @TableName nvarchar(255)
DECLARE @sqlStatement nvarchar(255)
DECLARE @tblRowCount int
CREATE TABLE #temp (table_name sysname,row_count int)

DECLARE @tableNameCursor CURSOR
SET @tableNameCursor = CURSOR FOR SELECT name from sys.tables
OPEN @tableNameCursor
FETCH NEXT FROM @tableNameCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @tableNameCursor INTO @TableName
select @sqlstatement= 'Select ''' + @TableName + ''', Count(*) from '+ @TableName
select @sqlStatement= 'Insert into #temp '+ @sqlstatement
exec(@sqlStatement)
END
CLOSE @tableNameCursor
DEALLOCATE @tableNameCursor

--Check to see the records
SELECT * FROM #TEMP


Using a stored procedure called sp_spaceused can get you more details along with count of rows as well space used for the table:

Drop table #TEMP

DECLARE @TableName nvarchar(255)
DECLARE @sqlStatement nvarchar(255)
DECLARE @tblRowCount int
CREATE TABLE #temp (table_name sysname,row_count int)


DECLARE @tableNameCursor CURSOR
SET @tableNameCursor = CURSOR FOR SELECT name from sys.tables
OPEN @tableNameCursor
FETCH NEXT FROM @tableNameCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @tableNameCursor INTO @TableName
select @sqlstatement= 'sp_spaceused '+ '"'+ @TableName +'"'
exec(@sqlStatement)
END
CLOSE @tableNameCursor
DEALLOCATE @tableNameCursor

SELECT * FROM #TEMP