Início / dba / Perguntas / 199947
Accepted
Francesco Mantovani
Asked: 2018-03-12 18:46:17 +0800 CST

INSERT MASTER.sys.sysdatabases resultam em uma tabela temporária

  • 772

Eu tenho essa consulta que itera todos os bancos de dados no meu 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

Mas como posso armazenar o resultado em uma tabela temporária?

sql-server temporary-tables

1 respostas

  1. Best Answer

    Dan Guzman estava certo (em um comentário que ele apagou) que você pode apenas usar master.sys.master_files, e que você certamente deve evitar visualizações antigas como sysdatabasese sysfiles, mas se você precisar fazer algo semelhante usando dados que não são convenientemente acumulados no master para você, você pode fazer algo assim:

    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;

    Observe nenhuma concatenação de strings desajeitada, sem mexer com 16 aspas simples consecutivas e o que acontece em seu código se um banco de dados for nomeado oh'crap? Eu mantenho bancos de dados como esse exatamente por esse motivo:

    Msg 105, Level 15, State 1, Line 1
    Aspas abertas após a cadeia de caracteres 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'.

    No entanto, você pode apenas alterar o script que você tem agora para fazer isso:

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

    Você também pode estar interessado no procedimento que escrevi, sp_foreachdb, pois certamente ao longo do caminho alguém sugerirá que você use sp_MSForEachDB, que não está documentado, sem suporte e terrivelmente quebrado.

    Também faz parte do kit de primeiros socorros de Brent Ozar .

    • 5

relate perguntas