主页 / dba / 问题 / 199947
Accepted
Francesco Mantovani
Asked: 2018-03-12 18:46:17 +0800 CST

INSERT MASTER.sys.sysdatabases 结果到一个临时表

  • 772

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

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

sql-server temporary-tables

1 个回答

  1. Best Answer

    Dan Guzman 是对的（在他删除的评论中），您可以只使用master.sys.master_files，并且您当然应该避免像sysdatabases和这样的旧视图，但是如果您需要使用不方便汇总到 master 中的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？正是出于这个原因，我保留了这样的数据库：

    消息 105，级别 15，状态 1，第 1 行
    字符串 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF' 后未闭合的引号。

    但是，您可以只更改您现在拥有的脚本来执行此操作：

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

    您可能还对我编写的过程感兴趣sp_foreachdb，因为肯定有人会建议您使用sp_MSForEachDB，它没有记录、不受支持且严重损坏。

    它也是 Brent Ozar 急救包的一部分。

    • 5

相关问题