Início / dba / Perguntas / 338989
Accepted
alper
Asked: 2024-04-25 20:57:54 +0800 CST

Execute `sp_spaceused` apenas para todos os bancos de dados

  • 772

A resposta seguinte ( Execute `sp_spaceused` para todas as tabelas em todos os bancos de dados ) executa a solução para sp_spaceusedtodas as tabelas em todos os bancos de dados.

Em vez disso, posso executá-lo apenas para todos os bancos de dados?

Consigo ver os resultados de todos os bancos de dados usando o script a seguir, mas, se possível, quero ver os resultados em uma única tabela do Excel, como a resposta vinculada:

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @SqlQuery NVARCHAR(MAX)

-- Declare a cursor to loop through all databases
DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' -- Filter out offline databases if needed

-- Open the cursor
OPEN database_cursor

-- Fetch the first database name from the cursor
FETCH NEXT FROM database_cursor INTO @DatabaseName

-- Start looping through all databases
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build the dynamic SQL query to switch database context and execute the query
    SET @SqlQuery = 'USE [' + @DatabaseName + ']; EXEC sp_spaceused @oneresultset = 1'

    -- Execute the dynamic SQL query
    EXEC sp_executesql @SqlQuery

    -- Fetch the next database name from the cursor
    FETCH NEXT FROM database_cursor INTO @DatabaseName
END

-- Close and deallocate the cursor
CLOSE database_cursor
DEALLOCATE database_cursor

Exemplo de saída de visualização do Excel, onde posso copiar facilmente todas as linhas e colá-las no arquivo Excel como um todo:

insira a descrição da imagem aqui

sql-server

2 respostas

  1. Best Answer

    Você pode construir uma UNION ALLconsulta dinâmica gigante usando sys.dm_db_partition_stats, com agrupamento e executá-la.

    DECLARE @sql nvarchar(max);

SELECT @sql = '
select
  name = concat_ws(N''.'', t.db_name, t.schema_name, t.name),
  [rows] = sum(case when t.index_id in (0, 1) then t.row_count end),
  [reserved_MB] = sum(t.reserved_page_count) * 8 / 1024.0,
  data_MB = sum(case when t.index_id in (0, 1) then t.used_page_count end) * 8 / 1024.0,
  index_size_MB = sum(case when t.index_id in (0, 1) then t.used_page_count end) * 8 / 1024.0,
  unused_MB = sum(t.reserved_page_count - t.used_page_count) * 8 / 1024.0
from (' + (
    SELECT STRING_AGG(CONVERT(nvarchar(max), N'
    select
      db_name = N' + QUOTENAME(d.name, '''') + ',
      o.name,
      o.schema_id,
      schema_name = sch.name,
      s.*
    from ' + QUOTENAME(d.name) + '.sys.dm_db_partition_stats s
    left join ' + QUOTENAME(d.name) + '.sys.objects o on o.object_id = s.object_id
    left join ' + QUOTENAME(d.name) + '.sys.schemas sch on sch.schema_id = o.schema_id
'), '
    union all
')
    FROM sys.databases d
    WHERE d.database_id > 4   -- only user DBs
      AND d.state IN (0, 5)   -- only online
) + '
) t
group by
  t.db_name,
  t.object_id,
  t.schema_id,
  t.name,
  t.schema_name;
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql;

    Se você quiser agregar também por banco de dados, você pode finalizar com:

    group by grouping sets (
 (
  t.db_name,
  t.object_id,
  t.schema_id,
  t.name,
  t.schema_name
 ),
 (
  t.db_name
 );

    Se você deseja agregar apenas por banco de dados, poderá simplificar significativamente:

    DECLARE @sql nvarchar(max);

SELECT @sql = '
select
  t.db_name,
  [rows] = sum(case when t.index_id in (0, 1) then t.row_count end),
  [reserved_MB] = sum(t.reserved_page_count) * 8 / 1024.0,
  data_MB = sum(case when t.index_id in (0, 1) then t.used_page_count end) * 8 / 1024.0,
  index_size_MB = sum(case when t.index_id in (0, 1) then t.used_page_count end) * 8 / 1024.0,
  unused_MB = sum(t.reserved_page_count - t.used_page_count) * 8 / 1024.0
from (' + (
    SELECT STRING_AGG(CONVERT(nvarchar(max), N'
    select
      db_name = N' + QUOTENAME(d.name, '''') + ',
      s.*
    from ' + QUOTENAME(d.name) + '.sys.dm_db_partition_stats s
'), '
    union all
')
    FROM sys.databases d
    WHERE d.database_id > 4   -- only user DBs
      AND d.state IN (0, 5)   -- only online
) + '
) t
group by
  t.db_name;
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql;
    • 2

  2. Basta executar o procedimento. Por padrão, ele é executado apenas para o banco de dados ao qual você está conectado.

    O exemplo na sua pergunta vinculada está usando o comando sp_msforeachdbpara realizar tarefas mais complicadas de listar o espaço usado para cada banco de dados.

    • 0

relate perguntas