DBA Diaries

I 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