title: SQL Server: How to create a temp table with unkown columns dataype/names?
body: <p>I need to make a query on multiple databases. I got the part for building the query for every database but now i need to put the result in something for each query then i can return it.</p>
  
  <p>@requete is a query</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[RequeteMultiBd]
      @requete varchar(max)
  AS
  BEGIN
  
      --get server + database name
      select dbo.trim(Serveur)+'.'+ dbo.trim(db) as name, row_number() over (order by db) seq into #tmp from dbo.DataBase;
  
      declare @name sysname
      declare @sql nvarchar(max)
      declare @seq int
      set @seq = 0
  
      --getting the next name and seq
      select top 1 @name = name, @seq = seq
              from #tmp where seq &gt; @seq order by seq
  
      set @sql = replace(@requete, '&lt;bd&gt;', @name);
      --Trying to init the table (that part doesnt work)
      insert into #result exec( @sql );
  
      --Filling up the table
      WHILE (1=1)
      BEGIN
          select top 1 @name = name, @seq = seq
              from #tmp where seq &gt; @seq order by seq
          if(@@rowcount = 0) break;
  
          set @sql = replace(@requete, '&lt;bd&gt;', @name);
          insert into #result exec( @sql );
  
      END
      select * from #result
  
  END
  </code></pre>
  
  <p>From that code i get this error because #result doesn't exit. I need to create it with dynamic column names but how ?</p>
  
  <blockquote>
    <p>Invalid object name '#result' </p>
  </blockquote>