Technical Article

Table row count using 3 different ways

,

Recently one of my friend was looking for a script to get row count in all user table, so we come up with 3 different ways of doing the same thing......

1. Using SysIndexes ROW column
2. Using un-documented "sp_MSforeachtable" procedure
3. Using old cursor way, loop through each table (count*) to get the count

Thanks

Mohit Nayyar

http://mohitnayyar.blogspot.com/

--I know we should NOT use system tables, Microsoft can change this anytime....
SELECT object_name (i.id) TableName, rows as RowCnt
FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U')
WHERE indid < 2
ORDER BY TableName 

--******************

--Undocumented procedure "sp_MSforeachtable"
CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
DROP TABLE #temp

--******************

--Old way, cursor loop through each table to get the count
--We can also use TABLE data type instead of temp. tables

SET NOCOUNT ON
DECLARE @tableName VARCHAR (255), @sql VARCHAR (300)
CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT)
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT INTO #temp (TableName, rowCnt) SELECT ''' + @tableName + ''' as tableName, count(*) as rowCnt from ' + @tableName)
FETCH NEXT FROM myCursor INTO @tableName
END
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
CLOSE myCursor
DEALLOCATE myCursor
DROP TABLE #temp

Rate

3.86 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

3.86 (7)

You rated this post out of 5. Change rating