counting records in all non system database table

is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?

Try this:

EXECsp_msforeachtable'sp_spaceused "?"'

|||

You can use the TSQL below to get this information and some others. The results is similar to SP_SPACEUSED.

SELECT object_name(id) AS name,
rowcnt AS rows,
reserved * 8 AS reserved_kb,
dpages * 8 AS data_kb,
(sum(used) * 8) - (dpages * 8) AS index_size_kb,
(sum(reserved) * 8) - (sum(used) * 8) AS unused_kb
FROM sysindexes
WHERE indid IN (0,1) -- cluster e n?o cluster
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, rowcnt, reserved, dpages
ORDER BY rowcnt DESC

|||

Hi,

Try the following procedure

Code Snippet

ALTER procedure USP_CountAllTables
AS

DECLARE @.TblName Nvarchar(255)
DECLARE @.SQL nvarchar(1000)
DECLARE CntCursor CURSOR FOR
select Name from sysobjects where xtype='U'
Set @.SQL=NULL

create table #Temp
(tableName nvarchar(255),
Record_Count int
)

OPEN CntCursor
FETCH NEXT FROM CntCursor
into @.TblName
WHILE @.@.FETCH_STATUS = 0
BEGIN

SET @.SQL='Select ''' + @.TblName + ''',count(*) from '+ @.TblName

insert #Temp
execute sp_Executesql @.SQL

FETCH NEXT FROM CntCursor
into @.TblName
END
CLOSE CntCursor
DEALLOCATE CntCursor
select * from #Temp
Drop table #Temp

This will give u the record count for all the user tables.

But its gonna take some time so have patience.

RegarDs,

Jacx