DEV Community

Cover image for SQL-Quick tip #9 - Number of rows in all tables
Allan Simonsen
Allan Simonsen

Posted on

3 2

SQL-Quick tip #9 - Number of rows in all tables

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Number of rows in all tables

When you start to work on a project for a new customer a good piece of information is how many tables does the database contain and how many rows are there in each table. Or maybe you don't have access to the production database and need to send a service request to the IT operations department to get information about the production database.
Then the query below will useful to you.

SELECT SCHEMA_NAME(sOBJ.schema_id) + '.' + sOBJ.name AS [Table name],
       SUM(sPTN.Rows) AS [Row count]
  FROM sys.objects AS sOBJ
  JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id
 WHERE sOBJ.type = 'U'
   AND sOBJ.is_ms_shipped = 0x0
   AND index_id < 2 -- 0:Heap, 1:Clustered
 GROUP BY sOBJ.schema_id, sOBJ.name
 ORDER BY [Table name]
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Image of AssemblyAI

Automatic Speech Recognition with AssemblyAI

Experience near-human accuracy, low-latency performance, and advanced Speech AI capabilities with AssemblyAI's Speech-to-Text API. Sign up today and get $50 in API credit. No credit card required.

Try the API

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay