How to create multiple tables on the fly so that every user each has his/her own set of tables?

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

To be honest, this is not really a good idea. Why don′t you store the just in one table with an identifier for the actual user which the data belong to. You will additionally have less pain finding the right table for the user and therefore need less coding logic. The naming part as you mentioned can′be done in SQL Server 2005 unless you create a schema for every user who will have the availbility to create a table. (SQL Server changed the objectowner to the Schema identifier) So I would better use the first option mentioned above to do the work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||The problem is, I need to create several tables per user this way, including a number of interrelated primary and foreign keys, for my webapplication. As mentioned, I can already do this for the dbo. using a stored procedure, but this would give the wrong behavior, because all users would then use the same set of tables. I want to give each user his/her own set of tables (dynamically) so that one user would not overwrite the stored work of another user...|||

i really dont know what you are up to but this is really a very, very bad idea.

please review database normalization first.

any way if you badly need it. here's a code to start with

use northwind
BEGIN TRANSACTION -- just in case you want to abort

--list the users

select IDENTITY(int, 1,1) AS ID_Num,name into #mytempx
from sysusers

create table modeltable change the table definition to the one you need
(
x varchar(10)
)

declare @.x int,@.MAX_ID INT,@.CMD NVARCHAR(200)
select @.x=0
SELECT @.MAX_ID=max(id_num)from #mytempX
while @.x<>@.MAX_ID loop thru the users and create each db
begin
select @.x=@.x+1
select @.CMD='SELECT * INTO [' + RTRIM(LTRIM( NAME)) + '] FROM MODELTABLE ' FROM #MYTEMPX
WHERE ID_NUM=@.X
SELECT @.CMD this is the create scripts
EXEC sp_executesql @.STATEMENT=@.CMD run the create scripts
end

drop table #mytempx
DROP TABLE MODELTABLE

SELECT * FROM GUEST -- test the create table

ROLLBACK TRANSACTION -- change to commit if you are very sure

regards,

joey

|||

OK, thanx for the info... I'm beginning to see that it would probably be easier to store the data I need as a BLOB (e.g. a serialized bunch of ArrayLists) instead of based on many tables per user. I then take the logic for the program away from the server (like it should be in a three tier solution, I guess), and that is probably your point as well...

Thanx for the suggestions nonetheless, it seems that this is far beyond my skills as a DB programmer...

Greetingz,

DJR