Anyone know table limits in multi-schema environment?

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!

any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?

thanks

-c

You definately can get over 1000 tables, since certain complex ERP systems and such have that just on their own, and that was even in older version of SQL Server.

According to :http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

The amount of tables is actualy only limited by the amount of objects (which means all triggers, tables, stored procedures, etc count toward this limit). The limit is... 2,147,483,647

Good luck busting that. Oh, and thats SQL Server 2000, was too lazy to find the 2005 specs :)|||

Sounds responable to me. I was going off this article from MS that suggested no more than 100 tables per client schema in a single db but they don't specify why :)

http://msdn2.microsoft.com/en-us/library/aa479086.aspx

Any idea how to do updates to the schema? my current thinking is to get my app to login as each schema owner and execute the update script.

thanks

-c