Could you point a tool, considering it exists, that could transform a complex query to a MER diagram by using MSSQLServer?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
Hi Experts

Could you point a tool, considering it exists, that could transform a complex query to a MER diagram?

Actually I'm doing surveys on a legate system to migrate the data contents to what will be  its correspondents on a CRM.

The queries is quite complex at first view.

SELECT * FROM ( SELECT at.id_atendimento, a.nome_assistido, 
  ( SELECT TOP 1 CONCAT( CASE WHEN c.ddd > 0 THEN CAST(c.ddd AS VARCHAR) COLLATE SQL_Latin1_General_CP850_CI_AI 
      ELSE '' END, c.contato) 
  FROM XXX.dbo.contato as c WHERE c.ativo_sn = 'S' 
    AND c.id_assistido = a.id_assistido 
    AND a.cpf <> CONCAT(CASE WHEN c.ddd > 0 THEN CAST(c.ddd AS VARCHAR) COLLATE SQL_Latin1_General_CP850_CI_AI ELSE '' END, c.contato) 
  ORDER BY ddd DESC, c.principal_sn DESC ) AS contato, 
  ol.descricao AS origem_ligacao, 
  (CASE WHEN a.id_titular IS NULL THEN 'R' ELSE 'D' END) AS tipo_usuario, 
    a.id_titular, at.id_usuario, at.id_servico, at.stamp_inicio_atendimento, 
    at.stamp_fim_atendimento, a.matricula, sni.id_solicitacao_novo_item, sni.id_tipo_status_solicitacao_novo_item, 
    sni.id_tipo_item_finalizado, aa.observacoes, aa.data_agendamento, a.cpf, pat.id_produto, at.atendimento_adverso, 
    at.id_tipo_responsavel, at.id_tipo_relacao, at.nome_responsavel, g.gerenciador, at.id_usuarioematendimento, 
    at.dataematendimento, at.divergencia_farmacia_sn, at.id_ligacao, at.motivo_contato, at.atendimento_em_aberto_sn, 
    at.semcontinuidade_sn, at.id_assistido, at.id_cliente, p.descricao as desc_produto, at.protocolo_atendimento, 
  (SELECT TOP 1 id_tipo_status_solicitacao_novo_item AS ultimo_status FROM XXX.dbo.solicitacao_novo_item AS subSni 
      WHERE subSni.id_atendimento = at.id_atendimento 
  ORDER BY subSni.id_solicitacao_novo_item DESC) AS id_ultimo_status, 
  ROW_NUMBER() OVER( ORDER BY (CASE WHEN aa.data_agendamento < DATEADD(minute,2,GETDATE()) THEN 1 
    WHEN aa.data_agendamento IS NULL THEN 2 ELSE 3 END) ASC, aa.data_agendamento DESC, 
          at.stamp_inicio_atendimento ASC, a.nome_assistido ) AS RowNr FROM XXX.dbo.assistido AS a 
        INNER JOIN XXX.dbo.atendimento AS at ON (at.id_assistido = a.id_assistido) 
        LEFT JOIN XXX.dbo.solicitacao_novo_item AS sni ON (sni.id_atendimento = at.id_atendimento) 
        LEFT JOIN XXX.dbo.gerenciador AS g ON (g.id_gerenciador = at.id_gerenciador) 
        LEFT JOIN XXX.dbo.atendimento_agenda AS aa ON (aa.id_atendimento = at.id_atendimento AND aa.ativo_sn = 'S') 
        LEFT JOIN XXX.dbo.produto_atendimento AS pat ON (pat.id_atendimento = at.id_atendimento) 
        LEFT JOIN XXX.dbo.produtos AS p ON (p.id_produto = pat.id_produto) 
        LEFT JOIN XXX.dbo.origens_ligacao AS ol ON (ol.id_origem_ligacao = at.id_origem_ligacao) 
  WHERE at.ativo_sn = 'S' AND at.atendimento_realizado_sn = 'S' AND at.stamp_inicio_atendimento <= '20190321 23:59:59' AND at.stamp_fim_atendimento IS NULL ) AS tempTable 
  WHERE 1 = 1 AND RowNr BETWEEN 1 AND 20 
  ORDER BY tempTable.id_atendimento DESC, tempTable.i d_solicitacao_novo_item DESC, tempTable.id_tipo_status_solicitacao_novo_item DESC

Open in new window


And dozen of others

Is there a tool to convert it to a diagram?

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
As far as I'm aware there is no such tool that converts MSSQL code to a ER diagram however...if you want to "see" what/how SQL does the work to produce the output record set you could use SSMS query tool and under the "query" menu option you can select "Display Estimated Execution Plan" and you'll see how things work inside SQL, if there are any missing indexes, ETC.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Thank you.

I'm convinced this kind of tool inexists.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial