2

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?

5

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.