Select Tablename from column name in other table
I have a database that makes new tables based on transactions everyday.
That activity is recorded in a table.
I can query that table called Statistics to see what table was created and when. So I can see that there was activity on table Qxxx134 and Qxxx473. The numbers are not sequential because tables are created over different databases. Still I can get the names.
If I go into table Qxxx134 I can see that the activity related to user Bob and user Sam
Here's the quandry: I want to update a field in the master user table to indicate activity. The field basically shows a join date. I can update that manually, but I'd like to do it dynamically.
So how can capture the usernames of users in the tables that have had activity in the last day?
I have the query to capture the tablenames and stored them in a new table called RecentActivity.
The column is TableName.
Any help would be appreciated.
Thanks.
That activity is recorded in a table.
I can query that table called Statistics to see what table was created and when. So I can see that there was activity on table Qxxx134 and Qxxx473. The numbers are not sequential because tables are created over different databases. Still I can get the names.
If I go into table Qxxx134 I can see that the activity related to user Bob and user Sam
Here's the quandry: I want to update a field in the master user table to indicate activity. The field basically shows a join date. I can update that manually, but I'd like to do it dynamically.
So how can capture the usernames of users in the tables that have had activity in the last day?
I have the query to capture the tablenames and stored them in a new table called RecentActivity.
The column is TableName.
Any help would be appreciated.
Thanks.
Brian Crowe
Unless you are storing some sort of relevant date in the table there is no way of telling when a given record was added or updated. One alternative is that when you create the tables you also create an insert/update trigger on that table to update your RecentActivity table.
ASKER
This is really more a of query or stored procedure issue.
I tried declaring the results of 1 table as a variable and then running another query
DECLARE @TNAME varchar(30)
SET@TNAME = (SELECT Top 1 TableName from RecentActivity)
--This gives me the first record in RecentActivity
Select User from * @TNAME
This is where I'm stumped.
If I run PRINT @TNAME it gives me the tablename of Qxxx123 but the select using @TNAME fails.
I tried declaring the results of 1 table as a variable and then running another query
DECLARE @TNAME varchar(30)
SET@TNAME = (SELECT Top 1 TableName from RecentActivity)
--This gives me the first record in RecentActivity
Select User from * @TNAME
This is where I'm stumped.
If I run PRINT @TNAME it gives me the tablename of Qxxx123 but the select using @TNAME fails.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it. It was the EXEC that I forgot. Thanks.