The below script using Cursor can be used to find the row count.
---Temporary table created for user tables
Select name,Xtype into #Temp from sysobjects where Xtype like 'U'
and name not in ('MSreplication_subscriptions','MSsubscription_agents')
DECLARE @TName sysname
DECLARE @Records nvarchar(3000)
DECLARE CCursor CURSOR READ_ONLY FOR
SELECT name from #Temp
OPEN CCursor
FETCH NEXT FROM CCursor
INTO @TName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @Records =
'SELECT ''' + @TName + '''as TableName, COUNT(*) as [No of Records] ' +
'FROM [' + @TName + ']'
EXEC SP_EXECUTESQL @Records
FETCH NEXT FROM CCursor INTO @TName
END
CLOSE CCursor
DEALLOCATE CCursor
