If you are using SQL Server 2008 or higher you can query the sys.partitions catalog view. The filed [rows] shows the number of records in each partition of the database table.
This query returns the schema and table names, and total number of rows. If you have only one partition for each table, you can wipe out SUM() and GROUP BY from the query.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT s.name AS schema_name, o.name AS table_name, SUM(p.[rows]) AS total_rows FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.partitions p ON o.object_id = p.object_id WHERE o.[type] = 'U' AND p.index_id < 2 GROUP BY s.name, o.name; |