SCRIPT: Find the row count of all tables and views

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

Advertisements

SQL Saturday US Map

Apparently, SQL Saturday isn’t popular in the west. See for yourself. I wanted to see what cities frequently host SQL Saturday and ended up creating this dashboard. There are some interesting findings. Look at how the events are primarily confined to only (right) half of United States. There are several states that haven’t yet seen a SQL Saturday!

image

I created this using Tableau Public. Unfortunately, functionality of the dashboard on this blog is limited, so I encourage you to click on this link to see an interactive map.

Which state has conducted the most events so far? That’s right, it’s the Sunshine state. Good guys in Florida are outstanding by conducting 20 events already.

I hope this post is fun. Your comments are welcome. Cheers!

I’m @SamuelVanga on Twitter.