Today I’m sharing another script that I often find useful – Queries to find the count of rows from tables and views in a SQL Server database.
Count Rows In All Tables:
This query returns the count of rows from all tables in a database. The results can be seen instantaneously without causing any blocking or locking. It is safe to run this in production.
; WITH A AS ( SELECT TableName = O.Name ,
SchemaName = SCHEMA_NAME(O.Schema_id) ,
[Rows] = P.Rows
FROM sys.objects O
INNER JOIN sys.partitions P ON O.OBJECT_ID = P.OBJECT_ID
WHERE TYPE = 'U'
)
SELECT SchemaName ,
TableName ,
SUM([Rows])
FROM A
GROUP BY SchemaName ,
TableName ;
Count Rows In All Views:
Unlike for tables, there is no way to query any DMV to get the row count. The only way is to perform COUNT() on all the views. The following query outputs a select for each view. Copy the results and execute in a query window.
SELECT fullname+' UNION ALL'
FROM sys.views v
INNER JOIN
sys.schemas s
ON s.schema_id = v.schema_id
CROSS APPLY (SELECT 'select '+''''+s.name+'.'+v.name+''''+','+ 'count(*) from '+s.name+'.'+v.name AS fullname) AS a
UNION ALL
SELECT 'SELECT '+'''DUMMY'''+', '+'0'
Caution: The query for views can be dangerously slow depending on the view’s definition. Please, please, please test it in your development before running it in production.
Sam Vanga 