INSERT MASTER.sys.sysdatabases result into a temporary table

Question

I have this query that iterate all databases on my SQL Server

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR 

SELECT name 
FROM MASTER.sys.sysdatabases

where name not in ('master','tempdb','model','msdb')

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

But how can I store the result all in one temporary table?

Answer 1

Dan Guzman was right (in a comment he deleted) that you can just use master.sys.master_files, and that you should certainly avoid old views like sysdatabases and sysfiles, but if you need to do a similar thing using data that is not conveniently rolled up into master for you, you can do something like this:

DECLARE @db sysname, @sql nvarchar(max), @exec nvarchar(max);

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT name FROM sys.databases
    WHERE database_id > 4
      AND state = 0;

OPEN c;

CREATE TABLE #files(db sysname, fn nvarchar(1000), sz int); 

SET @sql = N'INSERT #files(db,fn,sz) 
    SELECT @db, physical_name, size
    FROM sys.database_files;';

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
  EXEC @exec @sql, N'@db sysname', @db;
  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;   

SELECT db, fn, sz FROM #files;

DROP TABLE #files;

Notice no clunky string concatenation, no messing with 16 consecutive single quotes, and what happens in your code if a database is named oh'crap? I keep databases like this around for exactly this reason:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'.

However, you could just change the script you have right now to just do this:

INSERT #temptable(columns) EXEC sys.sp_executesql @Command;

You might also be interested in the procedure I wrote, sp_foreachdb, as surely along the way someone will suggest you use sp_MSForEachDB, which is undocumented, unsupported, and horribly broken.

• Making a more reliable and flexible sp_MSforeachdb

It's also part of Brent Ozar's First Responder Kit.