Today somone on SQLServerCentral.com posted a question – how to populate a temp table with a dynamic SQL query. The response I posted, shown below, is to use a global temp table, characterized by the double hash mark (##MyGlobalTemp).
The table will persist as long as you have a connection (spid) open that is accessing it. This may or may not be a good idea depending on your environment, but it will definitely work.
Regular temp tables are tricky to identify in metadata, since SQL Server will likely append some text to the name, to help keep the name unique. Global temp tables, however, are easier to identify, so it is easier to check for its existence beforehand and create a "safe" process.
DECLARE @s varchar(1000)
IF object_id('tempdb.dbo.##MyGlobalTemp') Is Not Null
DROP TABLE ##MyGlobalTemp
SET @s =
FROM (select name from sys.databases) as T'