http://www.sqlservercentral.com/blogs/samvangassql/2012/08/15/script-find-the-row-count-of-all-tables-and-views/

Printed 2014/07/25 03:16PM

SCRIPT: Find the row count of all tables and views

2012/08/15

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.

@SamuelVanga



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.