Average number of database tables (SQL Server)?

Question

I'm just wondering how many companies utilize 100+, 1,000+, or 10,000+ or more database tables/views/objects (but not stored procedures). I know this is a hard question to answer and that the utility of doing this depends on many factors, along with the company size and the type of application that the structures support.

But, I'm working at a company now on a team that supports 1 application with nearly 12,000 tables according to my query, and 17,000 tables according to my coworker (let's say it's 15k tables. I don't know why our numbers are different & I may not have access to everything he does).

My experience and understanding is that most companies "usually" have between maybe 20 to a few hundred database tables at most, which would normally power 1 or several software applications.

I'm trying to identify various complexities of projects being delayed within our (one, single) software application that produces so many tables and views. Am I right to estimate that 15k relational/SQL server views is a lot of tables and views? ...a lot more than what's typical or average?

I know it's a lot because our storage team refuses to back up our databases a lot of the time because it takes too long (like 10 or 15 hours). Is this really an absolute anomoly in the industry like I'm gathering? What's average, or an average range?

And no, our tables are not that normalized, which is in some cases out of our control since many are auto-generated by software applications (although I think it's somewhat within our control too which is another story).

Answer 1

I do agree that this is somewhat broad for SO but, none the less, will give you my opinion. I have never seen a database with more than 300 tables, yet they were 200+GB in size.

Depending on the nature of the application it is not unusual to have intermediary tables, where the data is stored and must be preserved. Use case scenarios include batch processing and reporting.

In either case it is important to clean-up these tables to keep DB size manageable. Data is usually copied away after a period of time into another database and preserved.

Auto creation of tables with numbers in the name (Table0001) smells of legacy DB design (circa 1998) and/or bad design practice. In old days this may have been done for performance reasons when generating complex reports, that could then be queried many times. I imagine dynamic SQL is rife in the application and debugging problems is a nightmare.

You need to find out what they are created for (reporting or some other reason) and find out the policy for removing "old" tables. If the tables are created for reporting purpose I would suggest to create a new database to store these tables.