Often database administrators and users want to know how many rows are there in each table or how big is the largest table in the database. Run the following SQL batch from your SQL query window to generates a list of all tables in your database together with the number of rows in each one. The list is sorted by table name but you can easily change the ORDER BY clause to sort it by the number of rows:
Syntax
USE db1
SELECT o.name as [Name], i.rowcnt as [Row Count]
FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE indid < 2 AND o.xtype = 'U'
ORDER BY Name
This query joins system tables to retrieve information about user tables (where xtype=’U’) and their row counts. You can modify the ORDER BY clause to sort by row count to quickly see your largest tables.
Beekeeper Studio Is A Free & Open Source SQL Server GUI
I’ve upgraded from SQL Server and it was a smooth change — Beekeeper Studio enhanced my own workstream, offering a cleaner experience and better performance, as well as flexibility. - Aman Bhattarai
Beekeeper Studio is a fast, intuitive and easy to use SQL Server client. It supports loads of other databases too, and works great on Windows, Mac and Linux.
What SQL Server Users Say About Beekeeper Studio
So far the most comprehensive DB admin GUI out there. The only reason I haven’t given 5 stars is I cannot claim to have used it exhaustively only having discovered it two weeks ago. But so far, mySQL, MSSQL and Postgres in one tool is simply incredible.
Planning to purchase once pay day hits.
I had been using the free version of Beekeeper studio for a while, but when I needed to work with some MongoDB databases I upgraded to the paid version. And honestly, I was up and running in no time. Beekeeper is slick, fast, and it just works. Very intuitive, very easy to use. I work with MongoDB, Postgresql, SQL Server, and other databases in my job, and having one tool that can talk to them all is just wonderful.
Thank you, Matthew and team!