How to use a temporary table variable (e.g. @temp) for holding the result of sp_msforeachdb

I am basically trying to find out all the table of all the databases present in the SQL SERVER by using sp_msforeachdb.

If I write exec sp_msforeachdb 'select "?" AS DatabaseNames,* from [?].sys.tables'

I will get the result well but for every Database, a seperate record set will be created.

Now if I use

CREATE TABLE #DBINFO
(
            DbName NVARCHAR(255)
            ,ObjectID NVARCHAR(255)
)

INSERT INTO #DBINFO
exec sp_msforeachdb 'select "?" AS DatabaseNames,ObjectID  from [?].sys.tables'

SELECT * FROM #DBINFO

This works fine.

However, if I do

Declare @DBINFO Table(DbName NVARCHAR(255),ObjectID NVARCHAR(255))
INSERT INTO @DBINFO
    exec sp_msforeachdb 'select "?" AS DatabaseNames,ObjectID  from [?].sys.tables'

   开发者_JAVA百科 SELECT * FROM @DBINFO

It does not accept

Moreover, If I do

;With CTE AS
(
  exec sp_msforeachdb 'select "?" AS DatabaseNames,* from [?].sys.tables'
)

Select * from CTE

, it throws error ( Presently I donot remember that).

Question is

1) Is it possible to do the same stuff using a table variable

2) Is it possible to get the same thing done using CTE?

SQL SERVER 2005.

Thanks

1) Yes, you just need to use the proper names for your columns (object_id, not ObjectID):

Declare @DBINFO Table(DbName NVARCHAR(255),ObjectID NVARCHAR(255)) 
INSERT INTO @DBINFO 
exec sp_msforeachdb 'select "?" AS DatabaseNames,object_id  from [?].sys.tables' 
SELECT * FROM @DBINFO 

2) No