将MASTER.sys.sysdatabases结果插入临时表

Question

我有一个查询，它迭代我的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

但是如何将结果全部存储在一个临时表中呢？

Answer 1

data是对的(在他删除的一条评论中)，您可以只使用master.sys.master_files，您当然应该避免使用sysdatabases和sysfiles这样的旧视图，但是如果您需要使用不方便地为您汇总的数据来做类似的事情，您可以这样做：

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;

注意，没有笨重的字符串连接，没有干扰16个连续的单引号，如果数据库名为oh'crap，代码中会发生什么？我保存这样的数据库正是因为这个原因：

Msg 105，第15级，状态1，字符串'as DB_name，SF.filename，SF.size FROM sys.sysfiles SF‘之后的第1行未闭引号。

但是，您可以更改您现在拥有的脚本，只需这样做：

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

您可能还会对我编写的过程sp_foreachdb感兴趣，当然，在使用sp_MSForEachDB的过程中，有人会建议您使用D9，这是没有文档的，没有支持的，而且非常糟糕。

• 制造更可靠、更灵活的sp_MSforeachdb

它也是布伦特·奥扎尔的第一应答器套件的一部分。