FAQ : Table row count from all the tables in the database

1. Simple Method using undocumented stored procedure sp_msforeachtable

CREATE TABLE #TableCount (tablename sysname, ROWCNT int)

INSERT INTO #TableCount

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

SELECT * FROM #TableCount where rowcnt>0

DROP TABLE #TableCount

Method 2 : Using SysIndexes and Sysobjects system tables

SELECT O.[name]
,I.rowcnt
FROM sysobjects O
JOIN sysindexes I
ON O.[id] = I.[id]
WHERE O.xtype = 'U'
AND O.status > 0
AND I.indid <2


Method 3. Using Cursors


Create proc spCountNoofRecords
as
Begin
SET NOCOUNT ON
create table #RecCount(TableName varchar(300),NoofRecords int)
Declare @RecCount1 int,@TableName varchar(300),
@SqlStatement nchar(2000),@ParmDefinition NVARCHAR(500)
DECLARE cRecCount CURSOR FOR
select Name from Sysobjects where xtype='u' order by name
OPEN cRecCount
FETCH NEXT FROM cRecCount INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @SqlStatement=N'Select @RecCount=count(*) from ['+@TableName +']'
SET @ParmDefinition = '@RecCount int OutPut'
EXECUTE sp_executesql @SqlStatement, @ParmDefinition,
@RecCount = @RecCount1 OUTPUT;
insert into #RecCount select @TableName,@RecCount1
FETCH NEXT FROM cRecCount INTO @TableName
END
CLOSE cRecCount
DEALLOCATE cRecCount
Select *From #RecCount
End