Wednesday, November 3, 2010

How to Get Row Count of All Tables - SQL Server

For this purpose doing a rowcount for each table will take forever, if the DB is fairly large. In both of following ways we don't count rows of each table, instead just look in SQL Server where it is already stored.

Query 1:
SELECT
[TableName] = O.name,
[RowCount] = MAX(I.rows)
FROM
sysobjects O,
sysindexes I
WHERE
O.xtype = 'U' AND I.id = OBJECT_ID(O.name)
GROUP BY O.name
ORDER BY [TableName] ASC


Query 2:
SELECT
'['+SCHEMA_NAME(T.[schema_id])+'].['+T.name+']' AS [Table_Name]
,I.rows AS Row_Count
FROM sys.tables T
INNER JOIN sysindexes I ON (T.object_id = I.id AND I.indid < 2
ORDER BY [Table_Name]


It should be noted that both of above queries give an approximate count only, because of sysindexes table is usually a little bit inaccurate, due to it is not updated constantly.

No comments: