Comparing Ways to Get Table Row Counts in MySQL and SQL Server

Get Row Counts MySQL SQL ServerI work in a mixed database server environment where applications are either powered by SQL Server, MySQL and more recently MongoDB. I started my career as a DBA working with SQL Server back in around 2003. In the last 5-6 years I have also been working with MySQL and more recently MongoDB which came into our organization last year.

In my time working with SQL Server it has become a mammoth product full of features and functionality. MySQL is not the same beast, however it is one of the most popular open source RDBMS in use today.

It is when I see something in MySQL that is straight forward to achieve but not so in SQL Server, that I scratch my head, wonder and hope that it will be simplified in future versions. I guess I presume too much.

An example of this was when I wrote up about a new feature in SQL Server 2012 for paging result sets using LIMIT – something which has existed in MySQL for a long time prior to SQL Server 2012.

I had another head scratching moment last week…..

As part of a task I was doing, I wanted to write some T-SQL to pull back a list of tables and their row counts. This task was across all databases on one of our servers. Typically I use the object explorer details view in Management Studio but I wanted to get the results out into a spreadsheet.

I wasn’t too bothered if the counts were accurate, more that I wanted to know which were populated by data. I also wanted a quick answer. I therefore was not prepared to use SELECT COUNT as it would be slow.

So I thought that this should be straight forward to achieve using INFORMATION_SCHEMA.TABLES, sadly it wasn’t or rather it wasn’t as easy as using MySQL.

So lets look at some ways to do this.

In MySQL, you can simply write something like this which is fast and approximate:


SELECT table_name, table_schema, table_rows
FROM information_schema.tables
WHERE table_schema not in ('mysql','performance_schema','information_schema');

To get the results out to a file by the way, you can run this from the command line (I’ve wrapped the lines for viewing purposes):


mysql -uroot -pYourPassword -e
"SELECT table_name, table_schema, table_rows
FROM information_schema.tables
WHERE table_schema not in
('mysql'
,'performance_schema'
,'information_schema')" > /path/filename.csv;

In SQL Server, querying INFORMATION_SCHEMA.TABLES does not bear the same fruit. Sadly, row counts are not displayed.

This is how I did it. I referenced SQL Server expert Pinal Dave’s article :)

There is usually someone out there who has found the answer already and has been very kind to share their solution with the world. They still had to invest some of their own spare time figuring it out though.


SELECT sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

This is a more work to achieve the same result.

On the plus side, if you don’t do what I did by searching the web, you haven’t read this or any other article on the subject and you have the time, you can figure it out yourself which will be a lot more rewarding personally.

There are other ways to achieve this, take a look at this excellent article too.

I think SQL Server it is a great product and in many ways a superior one to MySQL. It’s just the simple things that I like in life (and to see in all database server products) that keep me a happy DBA ;)

How to Install MySQL 5.5 on Ubuntu Server 12.04 LTS

For this how to, I am going to document how to install MySQL on an installation of Ubuntu 12.04 LTS Installing it is a very straight forward and quick process and I prefer to use the terminal. If you are running a GUI, you can also install it … [Continue reading]

How to Rename a SQL Server Column or Table Using T-SQL

I would typically use Management Studio to rename columns or tables in SQL Server. Afterall, it is an easy thing to do to open up the GUI make your changes and then save them back to the SQL Server. Have you ever been in a situation where you … [Continue reading]

Implementing a Simple SQL Server Backup Script

backup sql server command line

I've used this simple SQL Server backup script a number of times to backup sql server to a file or network drive. It's ideal if you are running SQL Server Express for example which does not come equipped with maintenance plans.  This script can be … [Continue reading]

Using DBCC SQLPERF

There are a number of uses for the DBCC SQLPERF command. I touched upon one of them in my last post on how you can use the command to obtain information about transaction log growth. This handy little DBCC command has been around for a while … [Continue reading]

Monitoring SQL Server Transaction Log Growth Using DBCC SQLPERF(logspace)

dbcc sqlperf(logspace)

For this article, I am using DBCC SQLPERF(logspace) to get size information about the transaction logs. I hope you will find this post useful in helping to monitor the growth of your transaction logs. The idea here is to capture and store the … [Continue reading]

An Introduction to SQL Server Backup

sql server backup database

Before I get into this post on SQL Server backup, I must make something really clear without sounding like I am stating the obvious. :| Having a fully tested backup and restore plan in place for your SQL Server databases is one of, if not, THE … [Continue reading]

Differences Between TRUNCATE TABLE and DELETE

If you want to remove data from a table, you can do this in a couple of ways using TRUNCATE TABLE or DELETE. A common approach is to use DELETE to remove all rows in the table but TRUNCATE TABLE offers an alternative and I will list what the … [Continue reading]

Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

dbcc freeproccache clear sql server plan cache

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for … [Continue reading]

The OLEDB Wait Type and How to Reduce It

reduce oledb wait type

In this post, I look at the OLEDB wait type. Books Online lists this as Occurs when SQL Server calls the SQL Server Native Client OLEDB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the … [Continue reading]

Have You Set Yourself Any Goals for the New Year?

"New year, new start" - I've heard that saying several times and it could mean the start of many different things. A new diet, a new fitness regime, a new job or complete career change. Giving up a vice, a new qualification, a new life - there … [Continue reading]