Need some help with tsql code

  • 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.


    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply