Displaying name of a table which is held in variable after executing a query with it

Question

I just wrote the following script. I'm wondering if it is possible to display the table name after each select statement so I can actually see from which table I have results.

Declare @tables table (table1 varchar(50));
declare @table varchar(50);
declare @tablename varchar(50);

INSERT INTO @tables select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where 
COLUMN_NAME ='order_id'

While (Select Count(*) From @tables Where table1 IS NOT NULL) > 0
BEGIN
set @table = (select top 1 table1 from @tables)

EXEC ('IF EXISTS (SELECT order_id from [' + @table + '] where order_id = 
13940001 ) 
SELECT * FROM [' + @table + '] where order_id = 13940001
'
) 


                    --!! Remember to put replace those order_ids with your 
order_ids on both these lines

delete from @tables where table1 = @table

END

I tried to do it like this:

Declare @tables table (table1 varchar(50));
declare @table varchar(50);
declare @tablename varchar(50);

INSERT INTO @tables select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where 
COLUMN_NAME ='order_id'

While (Select Count(*) From @tables Where table1 IS NOT NULL) > 0
BEGIN
set @table = (select top 1 table1 from @tables)

EXEC ('IF EXISTS (SELECT order_id from [' + @table + '] where order_id = 
13940001 ) 
SELECT * FROM [' + @table + '] where order_id = 13940001
SELECT @table'
) 


                    --!! Remember to put replace those order_ids with your 
order_ids on both these lines

delete from @tables where table1 = @table

END

But then I encounter this error:

Must declare the scalar variable "@TABLE".

And I don't know what to do about it.

Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) — Peter B, Oct 17 '17 at 15:08
@table doesn't exist outside of the session, you know that when you build your dynamic SQL above, so why not try how you have done it above SELECT @table? — Keith, Oct 17 '17 at 15:12

score 0 · Answer 1 · answered Oct 17 '17 at 15:17

This should give you the idea, your variable doesn't exist outside of the session, so you could include it in your select, so you know where each select comes from:

DECLARE @table NVARCHAR(255);
SELECT  @table = 'This is my table name'

PRINT ('IF EXISTS (SELECT order_id from [' + @table + '] where order_id = 
13940001 ) 
SELECT *,'+''''+@table+''''+' FROM [' + @table + '] where order_id = 13940001')

Produces:

IF EXISTS (SELECT order_id from [This is my table name] where order_id = 13940001 ) SELECT *,'This is my table name' FROM [This is my table name] where order_id = 13940001

score 0 · Answer 2 · answered Oct 17 '17 at 15:21

0

You have an answer in your question:

EXEC ('IF EXISTS (SELECT orderid from [' + @table + '] where orderid = 
13940001 ) 
begin
SELECT * FROM [' + @table + '] where orderid = 13940001
select ''' + @table + '''
end
')

answered Oct 17 '17 at 15:21

Aleksey Nesterov

28
5

Displaying name of a table which is held in variable after executing a query with it

2 Answers2