Problem Statement: I want to find row count of each table in a particular database using SQL query. On the basis of that result, I am going to forecast the space needed on the server where my database is located.
Solution : To find the row count of each table we can write below query( I am using SQL 2008).
SELECT SUM(ROW_COUNT) as Row_Count,OBJECT_NAME(A.OBJECT_ID) as TableName FROM SYS.DM_DB_PARTITION_STATS A INNER JOIN SYS.OBJECTS B
ON A.OBJECT_ID=B.OBJECT_ID WHERE INDEX_ID<2 AND B.TYPE=’U’
GROUP BY OBJECT_NAME(A.OBJECT_ID)
Lets Run the above query and see the result.
In the above screenshot, we can see the row count of each table in database, since we only needed the user table’s row count, hence we have put the condition (Type=’U’).
Note this query will not run in SQL server version (2000 or less), to find the row count of each table in a database version 2000, we can use below SQL Query.
SELECT ROWS, OBJECT_NAME(A.ID) FROM SYSINDEXES A INNER JOIN SYSOBJECTS B
ON A.ID=B.ID AND B.TYPE=’U’ AND INDID<2
The above query will give the same result in version SQL 2000.
Note: If we have 1 billion records in a table and we want to find the row count of that particular table, in that case also the above two query are very efficient in terms of performance. Hence instead of using below query to find the row count, we should use above mentioned queries.
SELECT COUNT(*) FROM EMPLOYEE — Don’t use
SELECT ROWS FROM SYSINDEXES WHERE INDID<2 AND ID=OBJECT_ID(‘EMPLOYEE‘)– Use This one.
Please feel free to add comments on this topic to share your views.
1,022 total views, 3 views today