Início / dba / Perguntas / 331666
Accepted
markzzz
Asked: 2023-09-28 15:16:06 +0800 CST

Como ordenar por COALESCE por alguns campos?

  • 772

Digamos que tenho esta consulta:

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

que extraem todas as tabelas de um banco de dados e o número de registros em cada tabela.

A pergunta é bem fácil, mas não consigo descobrir como fazer :) Como posso fazer o pedido por TableName? Ou RowCount?

Colocá-lo em ordem por cláusula me dá um erro (ou seja, sintaxe incorreta perto de 'SCHEMA_NAME'. ).

Obrigado por qualquer dica sofisticada.

sql-server

2 respostas

  1. Conforme mencionado, você precisa apenas adicionar ORDER BYà consulta dinâmica.

    Você não deve usar coalescência de variáveis ​​​​para agregar, é um antipadrão documentado e, na presença de coisas assim, ORDER BYgeralmente fornece resultados incorretos .

    Em vez disso, use apenas STRING_AGGcom um WITHIN GROUPparâmetro.

    Observe também o join to sys.schemase você pode usar sys.tablesem vez de filtrar sys.objectsdiretamente.

    DECLARE @QueryString NVARCHAR(MAX);
SELECT @QueryString =
  STRING_AGG(CONVERT(nvarchar(max), '
SELECT
  ''' + REPLACE(QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name), '''', '''''') + ''' AS TableName
, COUNT(*) AS RowCount
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK)
'),
    'UNION ALL ') WITHING GROUP (ORDER BY s.name, sOBJ.name)

    + '
ORDER BY TableName, RowCount;'

FROM sys.tables AS sOBJ
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE sOBJ.is_ms_shipped = 0;

EXEC sp_executesql @QueryString;

    Observe também que se você não se importa em obter números um pouco imprecisos, existem maneiras muito mais rápidas de obter contagens completas da tabela.

    Em versões mais antigas do SQL Server, você não tem, STRING_AGGentão precisa do hack clássico FOR XML PATHpara obter a mesma coisa e STUFFeliminar o separador extra.

    DECLARE @separator nvarchar(100) = 'UNION ALL ';

DECLARE @QueryString NVARCHAR(MAX);
SELECT @QueryString = STUFF((
  SELECT
    @separator + '
SELECT
  ''' + REPLACE(QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name), '''', '''''') + ''' AS TableName
, COUNT(*) AS RowCount
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK)
')

    FROM sys.tables AS sOBJ
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE sOBJ.is_ms_shipped = 0
    ORDER BY s.name, sOBJ.name
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, LEN(@separator), '') + '
ORDER BY TableName, RowCount;';

EXEC sp_executesql @QueryString;
    • 2
  2. Best Answer

    Depois de preencher @QueryStringvocê pode adicionar seu arquivo ORDER BY.

    DECLARE @QueryString NVARCHAR(MAX);
SELECT
         @QueryString = COALESCE(@QueryString + ' UNION ALL ', '') + N'SELECT '
                        + N'''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + N'.'
                        + QUOTENAME(sOBJ.name) + N''''
                        + N' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                        + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + N'.'
                        + QUOTENAME(sOBJ.name) + N' WITH (NOLOCK) '
FROM     sys.objects AS sOBJ
WHERE    sOBJ.type = 'U'
         AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id),
         sOBJ.name;

SET @QueryString = @QueryString + N' ORDER BY [TableName], [RowCount]';

EXEC sp_executesql @QueryString;
GO
    • 0

relate perguntas