Get list of table names in each database

I came across a situation where i have to gather information about all tables in each database onto a particular server. There were nearly 60 databases each with more than hundred tables. It was impossible for me to run a query for every single database and then merge the results.

In order to achive this, i created a temporary table to store database and table names. With help of undocumented stored procedure 'sp_msForEachDB' i stored the results into temporary table.

CREATE TABLE #t(dbName SYSNAME, tblName SYSNAME);

EXEC sp_msForEachDB 'INSERT #t

SELECT ''?'', TABLE_NAME

FROM [?].INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE=''BASE TABLE'';';

SELECT * FROM #t ORDER BY dbName,tblName;

DROP TABLE #t;

In order to understand 'sp_msForEachDB' command, refer to post 'Traversing
through every database: sp_MSforeachdb'