Need some help with tsql code

• Chitown

  Hall of Fame

  Points: 3239

  More actions

  September 17, 2021 at 4:18 pm

  Go to Answer

  #3930626

  I have 4 tables (DevDB, TestDB, StageDB, ProdDB). All those tables have 2 columns (DatabaseName, Environment). There are tons of DBs which exist on Prod but don't exist in lower environment. I need help with writing a query which pulls data so I can see that let's say 1 specific database exists in stage but not in prod or dev or test. I am not good at writing sql code so any help is highly appreciated.

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  September 17, 2021 at 4:23 pm

  Answer

  #3930627

   

  SELECT
      COALESCE(P.DatabaseName, D.DatabaseName, T.DatabaseName, S.DatabaseName) AS DatabaseName,
      CASE WHEN P.DatabaseName IS NULL THEN '' ELSE 'Yes' END AS Prod,
      CASE WHEN D.DatabaseName IS NULL THEN '' ELSE 'Yes' END AS Dev,
      CASE WHEN T.DatabaseName IS NULL THEN '' ELSE 'Yes' END AS Test,
      CASE WHEN S.DatabaseName IS NULL THEN '' ELSE 'Yes' END AS Stage
  FROM ProdDB P
  FULL OUTER JOIN DevDB D ON D.DatabaseName = P.DatabaseName
  FULL OUTER JOIN TestDB T ON T.DatabaseName IN ( P.DatabaseName, D.DatabaseName )
  FULL OUTER JOIN StageDB S ON S.DatabaseName IN ( P.DatabaseName, D.DatabaseName, T.DatabaseName )
  ORDER BY 1

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.