Finding the number of rows in every table in an SQL database

I recently finished an ecommerce site for a local shop. The site updated its own database from the database of the shop’s electronic point-of-sale (EPOS) system, which drives their tills.

This database was, erm, interesting to say the least. Most of the tables were named with what looked like a randomly picked string of characters, and there wasn’t a single foreign key reference anywhere. I had absolutely no clue which tables were even used, never mind which held the important data. The company who wrote the EPOS system weren’t very keen on helping out, wo I was on my own.

The first thing I wanted to do was find out how many rows were in each table. This would help me rule out the empty ones from the start. I messed around writing bits of code to generate SQL to do this, but then remembered that I could query the database schema from LinqPad, which would be much easier. The code there didn’t do exactly what I wanted, but it didn’t take long to modify it. The end result was quite concise and (once you are used to reading this stuff) fairly easy to understand. If you don’t agree, have a read of the blog post that inspired the one just linked, as I added more explanation there.

Mapping.GetTables()
  .Select(t => t.TableName.Replace("[", "").Replace("]", ""))
  .Select(t => {
    int n = 0;
    try {
      n = ExecuteQueryDynamic($"select count(*) from {t}").First();
    } catch { }
    return new {
      Table = t,
      N = n
    };
  }
  )
  .Where(t => t.N > 0)
  .OrderByDescending(t => t.N)

The penultimate line excludes tables without any rows, so you may want to remove that when first querying your database.

Another victory for the wonderful LinqPad!

Be First to Comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.