Rows per table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Rows per table


Is there a fast and easy way (ie one sql stmt, dbcc or system table lookup ) that will return in the number of rows in each table in a given database? The statement would need to be run against a database currently in use. Thank you
ekl
Select count(*) from table. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Not really ‘one statement’, but this is a short script that will give you the rowcount for each user table: DECLARE @TblName SYSNAME, @Result VARCHAR(100), @nSQL NVARCHAR(4000)
DECLARE tbls CURSOR FOR
SELECT [name] FROM dbo.sysobjects AS o WHERE OBJECTPROPERTY(o.[id], N’IsUserTable’) = 1
ORDER BY [name]
OPEN tbls
FETCH NEXT FROM tbls INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nSQL = ‘SELECT @1 = CAST(COUNT(*) AS VARCHAR(100)) FROM ‘ + @TblName
EXEC dbo.sp_ExecuteSQL @nSQL, N’@1 VARCHAR(100) OUTPUT’, @Result OUTPUT
PRINT (@tblName + ‘ – rowcount: ‘ + @Result)
FETCH NEXT FROM tbls INTO @TblName
END
CLOSE tbls
DEALLOCATE tbls

You can use this: SELECT object_name(id) ,rowcnt
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, ‘IsUserTable’) = 1 But this might result in wrong values sometimes so you should run DBCC UPDATEUSAGE command to correct inaccuracies in the sysindexes table.
HTH
-Rajeev Lahoty

You can run DBCC SHOWSTATISTICS which will show you the estimated no. of rows on the table. If you afford to run this query after defragmenting tha table it will give exact no. of rows. -Anup

These are great. Thank you all for your response. ekl
You can used following sql to find out no of rows in a table SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(table_name) AND indid < 2
Mahesh Paranjpe
SQL DBA
]]>