I've ran into this cryptic statement for SQL Server: Files Per Database 32,767. What does that mean exactly? Is there a maximum number of tables for a given version of SQL Server. We try to support SQL Server post 2005 32-bit and 64-bit.
So if anyone has a handy dandy table they use to figure out how many tables they can have per DB for Microsoft SQL Servers I'd heartily appreciate seeing it.
Best Answer
The number of tables is limited by the global size of the database and namespace only, as far as I know. So as long as you have the storage it should not matter. In any case your design is probably broken if you have a large amount of tables. Tables should only be used for data definition, not carry information about the data inside (which is the typical usecase when you generate large amounts of tables).
Files per database limitation comes into play when you are in a situation with multiple files/file group for the database. See the previous discussion at SQL Server 2005 / 2008 - multiple files / filegroups - how many? Why? for more information about files and file groups in sql server.