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: 101096

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".