Don't know temp tables when use Execute in SQL Server

Question

I have below Query :

if OBJECT_ID('tempdb..#tmpTables') is not null 
    drop table #tmpTables
Execute('select TABLE_NAME into #tmpTables from '+@dbName+'.INFORMATION_SCHEMA.TABLES')

while (select COUNT(*) from #tmpTables)>0
begin
    //here is my statement
end

When I execute this Query, I am getting this error :

Invalid object name '#tmpTables'.

But when the query is changed to this :

if OBJECT_ID('tempdb..#tmpTables') is not null 
    drop table #tmpTables
select TABLE_NAME into #tmpTables from INFORMATION_SCHEMA.TABLES
while (select COUNT(*) from #tmpTables)>0
begin
    //here is my code
end

It works.

How can I do this ?

Answer 1

Table names prefixed with a single number sign (#) are 'Local temporary table' names.

Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.
Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

And when you create a Local Temporary Table by EXEC() command the creator will not be you and it will disconnected after finishing the statement, And as finishing the connection temp table dropped.

---

You can use a table variable like this:

DECLARE @tmpTables TABLE(TABLE_NAME nvarchar(max))

insert into @tmpTables
(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)