[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

How can I reference a table?

I'd like to write a query wher the table name is not known in advance.  (Don't ask why:) .....  But the a some of the column names will always be the same.

I wish there was something like a pointer to a table.  

It would be something like:

Select *
from @table_name
where ....

Any ideas??  I was hoping for some magic from the system tables...
0
a222493
Asked:
a222493
  • 4
  • 4
  • 2
  • +1
1 Solution
 
arbertCommented:
Dynamic SQL.  Generally not a good idea:

declare @sql varchar(2000)
declare @table_name varchar(255)
select @table_name='yourtablename'

select @sql='select * from ' + @table_name

exec @sql
0
 
arbertCommented:
Oops, missed the parens:

exec @sql

should be:

exec(@sql)


I say it's generally not a good idea because of speed and security

0
 
a222493Author Commented:
I think I oversimplified the problem.  Here is a more complete explanation.  This is a really, really strange request....

1) Lets say I have 5 tables.  
2) There could be many more "data" tables like Table1, Table2... TableN
3) The data tables Table1, Table2...TableN always have at least three columns, which are:
      a) userName
      b) TaskID
      c) TaskName
4) The UserInfo table contains a userName field
5) TableNames contains all of the "Data" tables that I have (Table1, Table2, ...)
6) The data tables Table1, Table2... each have a field called userName which is a foriegn key.


userInfo
TableNames
Table1
Table2
Table3

I waould like to somehow write a query that would return all rows in each of the data tables for each user in UserInfo table.  I can achieve the desired result through nexted cursors and dynamic sql, but I was hoping for a single query possibly involving system tables...

declare @CurrentUser varchar (50)
declare @TableName varchar (50)
declare @sql varchar (8000)

select @CurrentUser = 'user1'  -- (This would actually be another cursor)

-- cleanup AllTasks
delete from AllTasks

-- Foreach TableName in TableNames
DECLARE cur_Table CURSOR FOR
SELECT       TableName from TableNames

OPEN cur_Table
FETCH NEXT FROM cur_Table INTO @TableName
      
WHILE @@FETCH_STATUS = 0
BEGIN
      select @sql = 'insert into AllTasks (TableName, TaskID, UserName, TaskName) '
      select @sql = @sql + 'Select ''' + @TableName + ''', TaskID, UserName, TaskName from ' + @TableName + ' Where UserName = ''' + @CurrentUser + ''''
      exec(@sql)
      print @sql
                  
      FETCH NEXT FROM cur_Table INTO @TableName
END
      
close  cur_Table
deallocate  cur_Table

-- Display the results
select * from AllTasks

--=====================
The results are:
Table1      1      user1         Table1Task1
Table2      3      user1      Table2Task3
Table3      1      user1      Table3Task1
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
arbertCommented:
Yes, that's quite a different question from what you posted above.....I definately wouldn't look at the system tables, but information_schema will provide the same information:

select * from information_schema.tables    '<--table names
select * from information_schema.columns '<-- column names

0
 
a222493Author Commented:
But can I use a join on information_schema or any other view/table and avoid the dynamic sql and cursors??
0
 
Anthony PerkinsCommented:
Please close this abandoned question:
http://www.experts-exchange.com/Databases/Reporting_Tools/Q_21364790.html

Thanks,
Anthony
0
 
a222493Author Commented:
acperkins,

you seem to have an unhealthy obsession in the status of my posts (see the previous dozen or so times you have made requests for me to close posts...)  You don't seem to be a moderator (http://www.experts-exchange.com/help.jsp#hi79), so I'm not sure how you feel that you are entitled to give others unsolicited advice.  I do not need, and do not want your help on ANY issues in the future.  Please do not post to me again.  This is a request which I hope you will honor.


0
 
Anthony PerkinsCommented:
>>you seem to have an unhealthy obsession in the status of my posts<<
I am sorry you take it that way.  You may find experts here more responsive if you take the time to maintain your open questions.  We are here to help you, help yourself by complying with the EE Guidelines that you agree to when you sign on.

>>You don't seem to be a moderator<<
Never claimed to be.  I am just a member of this community like yourself, who has spend a lot of time cleaning up abandoned questions, such as yours.

>>so I'm not sure how you feel that you are entitled to give others unsolicited advice.<<
See above.

>>I do not need, and do not want your help on ANY issues in the future.<<
Than perhaps you are in the wrong place.

Good luck.
0
 
rafranciscoCommented:
If I understood your requirements correctly, you can go away with cursors but not with dynamic SQL:

DECLARE @vSQLStmt   VARCHAR(8000)

SELECT @vSQLStmt = ISNULL(@vSQLStmt + ';', '') + 'INSERT INTO AllTasks (TableName, TaskID, UserName, TaskName) SELECT ''' + TableName + ''', TaskID, UserName, TaskName FROM ' + TableName + ' A WHERE EXISTS (SELECT ''X'' FROM UserInfo B WHERE A.UserName = B.UserName)'
FROM TableNames

EXEC (@SQLStmt)

This basically builds a list of INSERT statements for each TableName in your TableNames table separated by a semi-colon.  The INSERT statement will look like this:

INSERT INTO AllTasks (TableName, TaskID, UserName, TaskName)
SELECT 'YourDataTable', TaskID, UserName, TaskName
FROM YourDateTable A
WHERE EXISTS (SELECT 'X' FROM UserInfo B WHERE A.UserName = B.UserName)

You don't have to create a cursor for the User Name and this is already addressed by the WHERE clause here.

Hope this helps.
0
 
a222493Author Commented:
rafrancisco,

Wow.  That's very slick.  However, I'm just worried about the 8000 character limit if I get to many etries in the TableNames table.

I will award you full points if I don't get any other solutions in about a day or so.

Thanks for your help!
0
 
arbertCommented:
"Wow.  That's very slick.  However, I'm just worried about the 8000 character limit if I get to many etries in the TableNames table"

You can always declare several variables at varchar(8000) and then concat them on the execute without problems...
0
 
rafranciscoCommented:
The length of a single INSERT ... SELECT statement above is at most 250 characters.  That would be enough for 32 data tables.

Here's another way to do it to accommodate more that 32 data tables:

DECLARE @vTableNames   VARCHAR(8000)
DECLARE @vSQLStmt        VARCHAR(1000)
DECLARE @vTableName    VARCHAR(50)

SELECT @vTableNames = ISNULL(@vTableNames + ',', '') + TableName
FROM TABLENAMES

WHILE LEN(@vTableNames) > 0
BEGIN
        SET @vTableName = CAST(LEFT ( @vTableNames , ISNULL(NULLIF(CHARINDEX(',', @vTableNames ) - 1, -1), LEN(@vTableNames ))) AS VARCHAR(10))
        SET @vTableNames = SUBSTRING ( @vTableNames , ISNULL(NULLIF(CHARINDEX(',', @vTableNames ), 0), LEN(@vTableNames )) + 1, LEN(@vTableNames ))

       SET @vSQLStmt = 'INSERT INTO AllTasks (TableName, TaskID, UserName, TaskName) SELECT ''' + @vTableName + ''', TaskID, UserName, TaskName FROM ' + @vTableName + ' A WHERE EXISTS (SELECT ''X'' FROM UserInfo B WHERE A.UserName = B.UserName)'

       EXEC (@vSQLStmt)
END
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now