Count tables in a SQL Database

Today I hit an odd error with Entity Framework 6, finding myself with a need to Count tables in a SQL Database.

The error occurred when … Well I don’t really know.

I was debugging an application we’ve been working on for a number of months now when I hit a (somewhat juvenile) mistake on one of the pages. I’d called a parameter based constructor from inside a Linq query.

Oops.

So I stopped debugging and fixed the statement, to use the parameter less constructor, making a small tweak in the following code to handle the assignments myself, and I performed a Clean, followed by a Build, and then I hit debug again, to which I was given the following;

Model backing the context has changed

I was puzzled as to what had changed, so I created a migration to take a look. And what I got shocked me. The code migration created (Which I had called ‘What-The-F*ck-Has-Changed’ out of frustration’) was dropping my entire database, every relationship, index and table. So I dug a little deeper to see what EF6 wasn’t detecting through the context.

In order to do so, I found myself in need for a quick count of all the database tables, to compare against the EF6 model to find out what had went wrong. I used the following script to count tables in a SQL database backing the application;


USE DatabaseName
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

The count returned a value differing to the EF context, which was missing the relationship tables (I found after digging a little deeper). However, this was one situation where it helped to count tables in a SQL database.

But What Happened?

Your guess is as good as mine. Removing my temporary files (Now a batch script it’s needed that often) and firing off a rebuild seemed to remove the problem. In all, I just wanted to share the SQL for counting the tables within a database.

P.S

In order to discover the relationship tables where missing from the context (It wasn’t just relational tables missing, it was also 3 standard tables) I first stepped-through the application to find the set of models recognized by the context and compared that to the database tables, using Excel. To get the list of database table names to copy straight to excel, I used the following script;


USE DataAccess
SELECT * FROM information_schema.tables

About Adam Riddick

When not Developing, Adam moonlights as a climbing frame for his two children. Adam Riddick was founded in 1987, in County Durham, England. Read More ...

10. November 2014 by Adam Riddick
Categories: SQL | Leave a comment

Inspire me with your wisdom ...