Início / dba / Perguntas / 118017
Accepted
Ravi
Asked: 2015-10-15 05:53:30 +0800 CST

Qual seria a maneira mais eficiente de verificar valores nulos em todas as colunas para várias tabelas?

  • 772

Digamos que eu tenha três tabelas ou quatro tabelas que desejo verificar se há valores presentes e não quero que nenhuma delas seja nula. SE forem nulos, enviarei uma notificação por e-mail por linha. Qual seria a melhor maneira de fazer isso? Devo fazer um procedimento armazenado com instruções IF separadamente?

Digo isso porque todas essas tabelas não têm restrições, apenas extraem dados de uma planilha do Excel. Portanto, criar uma variável de tipo de tabela e união ou junção interna não retorna nada, pois até mesmo os nomes das colunas são diferentes, mas as informações que elas armazenam são o mesmo tipo de informação. Assim, por exemplo, se em uma tabela existe uma coluna chamada 'DueDate', na outra tabela, esse mesmo tipo de informação é chamada 'PaymentDate'. Como eu executaria tal verificação em todas essas tabelas?

Atualização: aqui está o que pensei até agora: digamos que as duas tabelas sejam A e B, então

DECLARE @messageBody NVARCHAR(MAX)
DECLARE @iterator int
DECLARE @Rowcount int
DECLARE @varTableA TABLE(tablecounter int IDENTITY(1,1), DueDate date, Account NVARCHAR(20), Retailer NVARCHAR(20), Interest float)


INSERT INTO @varTableA (DueDate, Account, Retailer, Interest)
SELECT DueDate, Account, Retailer, Interest
FROM TableA 
WHERE DueDate IS NULL OR Account IS NULL OR Retailer IS NULL OR Interest IS NULL

SET @Rowcount = @@ROWCOUNT
SET @iterator = 1


WHILE(@iterator <= @Rowcount)
BEGIN 

SET @messageBody = (SELECT 'No values in following columns' + 'Duedate: ' + ISNULL(Duedate, 'No value') + CHAR(10) + CHAR(13) 
+ 'Account: ' + ISNULL(Account, 'No value') + CHAR(10) + CHAR(13)
+ 'Retailer: ' + ISNULL(Retailer, 'No Value') + CHAR(10) + CHAR(13)
+ 'Interest: ' + ISNULL(Interest, 'No Value') + CHAR(10) + CHAR(13)

                    FROM @varTableA 
                    WHERE @tablecounter = @iterator)

EXEC msdb.dbo.sp_send_dbmail @profilename = [name], @recipients = [reclist], @subject = [sub], @body = @messageBody
SET @iterator = @iterator + 1
END

E para repetir isso no mesmo ou em um procedimento armazenado separado para TableB e Table C. Existe uma maneira melhor de fazer isso ou você acha que estou fazendo isso da maneira certa?

sql-server sql-server-2008

2 respostas

  1. Best Answer

    Para a parte 1 da sua pergunta , criei essas três tabelas em um banco de dados vazio:

    CREATE TABLE dbo.table1(a INT, b INT);
CREATE TABLE dbo.table2(a INT, b INT);
CREATE TABLE dbo.table3(a INT, b INT);

INSERT dbo.table1(a,b) VALUES(1,1),(1,2),(1,NULL);
INSERT dbo.table2(a,b) VALUES(1,1),(NULL,NULL),(1,NULL);
INSERT dbo.table3(a,b) VALUES(1,1),(1,2),(1,2);

    Então eu escrevi este código para extrair vários bits de NULLinformação:

    CREATE TABLE #x
(
  t           NVARCHAR(512),                     -- table name
  nullrows    INT NOT NULL DEFAULT 0,            -- number of rows with at least one NULL
  nullvalues  INT NOT NULL DEFAULT 0,            -- total NULL values in table
  nulldist    NVARCHAR(MAX) NOT NULL DEFAULT N'' -- how many NULLs in each column
);

INSERT #x(t) VALUES(N'dbo.table1'),(N'dbo.table2'),(N'dbo.table3');

    Agora um pouco de SQL dinâmico derivado de metadados:

    DECLARE @sql NVARCHAR(MAX) = N'';


SELECT @sql = @sql + N'
  UPDATE #x SET nullrows = nullrows 
    + (SELECT COUNT(*) FROM ' + t.t 
    + N' WHERE (' + STUFF((SELECT N' OR ' 
    + QUOTENAME(c.name) + N' IS NULL'
  FROM sys.columns AS c
  WHERE OBJECT_ID(t.t) = c.[object_id]
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),
    1, 4, N'') + N')) WHERE t = N''' + t.t + N''';'
FROM #x AS t;

EXEC sys.sp_executesql @sql;
SET @sql = N'';

SELECT @sql = @sql + N'
  IF EXISTS (SELECT 1 FROM ' + t + N' WHERE ' 
    + QUOTENAME(c.name) + N' IS NULL)
  UPDATE #x SET nullvalues = nullvalues 
    + (SELECT COUNT(*) FROM ' + t + N' WHERE ' 
    + QUOTENAME(c.name) + N' IS NULL),
    nulldistribution = nulldistribution + '''
    + c.name + N':'' + RTRIM((SELECT COUNT(*) FROM ' 
    + t + N' WHERE ' + QUOTENAME(c.name) + N' IS NULL))
    + N'','' WHERE t = N''' + t + N''';'
  FROM #x AS t
  INNER JOIN sys.columns AS c
  ON OBJECT_ID(t.t) = c.[object_id];

EXEC sys.sp_executesql @sql;

SELECT * FROM #x;

    Resultados:

    t             nullrows   nullvalues   nulldistribution
dbo.table1    1          1            b:1,
dbo.table2    2          3            a:1,b:2,
dbo.table3    0          0

    Vou deixar como exercício para o leitor como enviar um e-mail com base nesses resultados - essa não parece ser exatamente a parte mais difícil.

    Para a parte 2 da sua pergunta , como eu disse em um comentário:

    Não há nenhum código que você possa escrever que diga que DueDate e PaymentDate contêm o mesmo tipo de informação, além de verificar se eles compartilham o mesmo tipo de dados. É para isso que servem o controle de origem e a documentação.

    Eu diria que esta é uma pergunta diferente e sem resposta.

    • 5

  2. A maioria das operações em um NULL retornará NULL. Você pode usar isso para recolher todos os valores de coluna em um único valor que é NULL se uma ou mais colunas forem NULL.

    create table t1 (id int NOT NULL, a int, b varchar(10), c date);
go

insert t1(id, a, b, c)
values (1, 1, 'a', '2015-10-01'), 
       (2, NULL, 'a', '2015-10-01'), 
       (3, 1, NULL, '2015-10-01'), 
       (4, 1, 'a', NULL);
go

select * from t1;
go

;with x as
(
    select
        ID, convert(varchar(max), a)
          + convert(varchar(max), b) 
          + convert(varchar(max), c) as HasNull
    from t1
)
select
    ID
from x
where x.HasNull is null;

go
drop table t1;

    Esta solução executará uma única verificação de tabela em cada tabela uma vez por execução. A saída será uma lista dos IDs de linha que possuem um ou mais valores NULL. Presumo que você deva ter uma coluna de chave primária não anulável. Caso contrário, como você indicaria ao destinatário do e-mail qual linha estava com problemas?

    Veja a resposta de Aaron sobre como você pode construir isso dinamicamente em tempo de execução nas tabelas do sistema.

    Seu e-mail de amostra informa ao destinatário exatamente quais colunas são NULL. Eu sacrifico isso por uma consulta mais compacta. Depois que o usuário abre os dados em um editor e navega até a chave indicada, deve ficar claro quais colunas contêm NULL.

    • 1

relate perguntas