query multiple tables named from values in another table 1

[bogjacket]

Let's see if I can make sense of this...

I have a user table, holding the names of active users:
-dbo.jsmith
-dbo.johnson
-dbo.echarles
...

I also have a history table for EACH user, and these tables are named "history" & the username:
-dbo.historyjsmith
-dbo.historyjohnson
-dbo.historyecharles
...

Each time a user accesses an account, the program creates a new row in the user's history table, listing the account number and datetime accessed. A single account may be accessed by multiple users, multiple times. So, the account number may appear several times in several history tables.

I'm looking for a way to easily search all history tables for occurences of a particular account number, so that I can see who has accessed any account I choose.

Is there a way to do this without running a query for each history table?

By the way, this is running on SQL server 2005.

Thanks for your help!

 

[bogjacket]

I'm sorry. When I wrote:
"-dbo.jsmith
-dbo.johnson
-dbo.echarles"

I meant that each username is in it's own row all in the same usertable.

There is one user table, and a history table for each username contained within the user table.

 

[rac2]

No.

But you could write a procedure to automate this, using dynamic SQL and a temporary table.

Create a temporary table with columns for whatever you wish to obtain about each historical event.

Create a cursor from the table of active users.

Step through the cursor and build a query for the user history. EXECUTE that query to insert rows into the temporary table for the particular account.


After stepping through all users, query the temporary table for the results over all users.

DECLARE @sqlQuery VARCHAR(200)
DECLARE @sqlInsert VARCHAR(200)
SET @sqlInsert ='INSERT #tempDetails ( accountid, userid, dt_accessed, etc) '
...
/*DECLARE CURSOR an loop through it*/
...
SET @sqlQuery = ' SELECT * FROM ' + @userTable + ' WHERE acccount = ' + @accountid
EXECUTE(@sqlInsert + @sqlQuery)
...
/*After the loop is finished*/
SELECT * FROM #tempDetails ORDER BY dt_accessed, userid

 

[bborissov]

Other way is just to have ONE table that will keep your USER IDs and all other info you need. then query that table and join it with you users one.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bogjacket]

Thanks rac2.

This above the level of anything I've yet to do with SQL, but I'm glad to have an excuse to learn more. I'll get started on this today.

bborissov,

I understand what you're saying, but these tables are part of a larger third-party software that I did not build.

Thank you both!