Cleaning Up That Rat’s Nest of T-SQL

The following sites have a very good pretty printers:

http://www.tsqltidy.com

  • For whatever reason, sometimes this site “hangs” and doesn’t do anything.

http://www.dpriver.com/pp/sqlformat.htm

  • This site actually sells a pretty printer that you can run locally, in addition to the above web based site.

Many of the apps I work with are dynamic query generators with anywhere from 20 to 80 tables in a join. It’s code by a machine, for a machine.

It will turn this incomprehensible string…

SELECT SDSOQS,SDLTTR,SDPRP2,SDADTM,SDQTYT,SDRSDT,SDSHPN,SDCORD,SDUOM4,SDPRP1,SDSRP1,SDSHAN,SDUOM2,SDPDDJ,SDOPDJ,SDKCOO,SDSFXO,SDLOCN,SDDELN,SDDRQJ,SDPSN,SDSO16,SDPOE,SDDRQT,SDAN8,SDFEA,SDPEND,SDSHCCIDLN,SDSOCN,SDSPATTN,SDTHGD,SDRKCO,SDOPTT,SDPMTN,SDCOMM,SDOORN,SDSRP3,SDVR02,SDDOC,SDLNID,SDURAT,SDCRCD,SDASN,SDRSDJ,SDAEXP,SDPRP5,SDPRP3,SDPEFJ,SDUORG,SDOCTO,SDTORG,SDSONE,SDPPDJ,SDDOCO,SDDCTO,SDCNDJ,SDSOBK,SDZON,SDSWMS,SDTHRP,SDUPRC,SDPDTT,SDMOT,SDUOM,SDRCTO,SDCRR,SDPSIG,SDDSC2,SDDGL,SDDCT,SDADDJ,SDRORN,SDDMCT,SDNXTR,SDSO12,SDTRDJ,SDEMCU,SDAAID,SDVR01,SDALLOC,SDLOTN,SDRFRV,SDDSC1,SDITM,SDCARS,SDSRP5,SDLNTY,SDLITM,SDPRIO,SDUNCS,SDRKIT,SDRLLN,SDRLIT,SDOGNO,SDMCU,SDKCO,SDTPC,SDSRP2,SDDVAN,SDKITID,SDANBY,SDNUMB,SDFRTH,SDAITM,SDFRMP,SDSQOR,SDPMTO,SDPRP4,SDPA8,SDSRP4,SDIVD,SDOKCO,SDFRGD,SDFUC,SDFUP,SDSO15 FROM PRODDTA.F4211 WHERE ((((((SDLTTR >= @P0  AND SDLTTR <= @P1 ) AND SDLITM LIKE @P2 ) AND SDNXTR < @P3 ) AND SDSOBK > @P4 ))) UNION SELECT SDSOQS,SDLTTR,SDPRP2,SDADTM,SDQTYT,SDRSDT,SDSHPN,SDCORD,SDUOM4,SDPRP1,SDSRP1,SDSHAN,SDUOM2,SDPDDJ,SDOPDJ,SDKCOO,SDSFXO,SDLOCN,SDDELN,SDDRQJ,SDPSN,SDSO16,SDPOE,SDDRQT,SDAN8,SDFEA,SDPEND,SDSHCCIDLN,SDSOCN,SDSPATTN,SDTHGD,SDRKCO,SDOPTT,SDPMTN,SDCOMM,SDOORN,SDSRP3,SDVR02,SDDOC,SDLNID,SDURAT,SDCRCD,SDASN,SDRSDJ,SDAEXP,SDPRP5,SDPRP3,SDPEFJ,SDUORG,SDOCTO,SDTORG,SDSONE,SDPPDJ,SDDOCO,SDDCTO,SDCNDJ,SDSOBK,SDZON,SDSWMS,SDTHRP,SDUPRC,SDPDTT,SDMOT,SDUOM,SDRCTO,SDCRR,SDPSIG,SDDSC2,SDDGL,SDDCT,SDADDJ,SDRORN,SDDMCT,SDNXTR,SDSO12,SDTRDJ,SDEMCU,SDAAID,SDVR01,SDALLOC,SDLOTN,SDRFRV,SDDSC1,SDITM,SDCARS,SDSRP5,SDLNTY,SDLITM,SDPRIO,SDUNCS,SDRKIT,SDRLLN,SDRLIT,SDOGNO,SDMCU,SDKCO,SDTPC,SDSRP2,SDDVAN,SDKITID,SDANBY,SDNUMB,SDFRTH,SDAITM,SDFRMP,SDSQOR,SDPMTO,SDPRP4,SDPA8,SDSRP4,SDIVD,SDOKCO,SDFRGD,SDFUC,SDFUP,SDSO15 FROM PRODDTA.F42119 WHERE ((((((SDLTTR >= @P5  AND SDLTTR <= @P6 ) AND SDLITM LIKE @P7 ) AND SDNXTR < @P8 ) AND SDSOBK > @P9 ))) ORDER BY 54 ASC  , 55 ASC  , 16 ASC  , 100 ASC  , 40 ASC

Into something you can actually read and work with:

SELECT   SDSOQS,
         SDLTTR,
         SDPRP2,
         SDADTM,
...
         SDFRGD,
         SDFUC,
         SDFUP,
         SDSO15
FROM     PRODDTA.F4211
WHERE    ((((((SDLTTR >= @P0
               AND SDLTTR <= @P1)
              AND SDLITM LIKE @P2)
             AND SDNXTR < @P3)
            AND SDSOBK > @P4)))
UNION
SELECT   SDSOQS,
         SDLTTR,
         SDPRP2,
         SDADTM,
         SDQTYT,
         SDRSDT,
...
         SDDCT,
         SDFUP,
         SDSO15
FROM     PRODDTA.F42119
WHERE    ((((((SDLTTR >= @P5
               AND SDLTTR <= @P6)
              AND SDLITM LIKE @P7)
             AND SDNXTR < @P8)
            AND SDSOBK > @P9)))
ORDER BY 54 ASC, 55 ASC, 16 ASC, 100 ASC, 40 ASC;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s