Thank you for sharing your approach! Am I correct in saying that this would not work if multiple users happen to run this simultaneously? (because of the global temp tables). If so, is there a work-around, which would help?
You are correct: this approach would definitely fail if multiple users run it at once. One solution would be to tag the name of the global temp table with a unique suffix, a timestamp maybe. So instead of the table being called ##database_files, you could dynamically name it and call it something like ##database_files_12032014082319123, or something like that. It's a bit roundabout, but would work nicely to avoid duplication.
An alternative would be to have the run_query_across_databases query execute a SELECT, instead of an INSERT. Define a local temp table, #database_files. Then, use syntax such as this to insert directly into it:
INSERT INTO #database_files
EXEC run_query_across_databases '
1, NULL, NULL, NULL, @my_db_list
This isn't thoroughly tested, but with a few changes would allow you to avoid global temp tables altogether and allow multiple users to run it at once.
I personally would prefer the dynamically-named table, as the date/time tagging can be a useful debugging tool, but that is a matter of opinion : )