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

Any type of Audit mechaninsm in SQL Server?

Does anyone know if there is any type of audit trail mechanism used in SQl Server 2000 or 20005?

In our production environment, some of our stored procedures, views and data in our tables has changed. It would be nice to have a footprint of what was changed by whom and one.

For our code we use source safe but for SQL Server I am not aware of any type of auditing mechanism.

AFAIK, there is no built in or an add-on from MS for auditing proc or schema changes in SQL Server. Either you need to manually control the change process (via custom app or VSS) or use some third party tools.