T-SQL formatting tool

  • Does anyone know of a t-sql formatting tool that is compatible with SSMS 2016 and will format BACKUP/RESTORE statements?

    I've got ApexSQL's tool which is really good but it doesn't format BACKUP/RESTORE statements; which whilst a bit of a pain isn't the end of the world but if there's a tool out there that will format those statements, that would be great.

    Andrew

  • Have you already tried RedGate's SQL Prompt?

  • For one-off or infrequent formatting, there are several web-based options.

    http://www.bing.com/search?q=online+t-sql+formatter

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Had a look at SQL prompt, bit pricey for a formatter isn't it?

    I don't really want to use an online tool, not really that convenient.

    It's a shame as the poor man's t-sql formatter worked really well but it's not compatible with the new version of SSMS.

  • Have you tried DBforge SQL complete?

    ETA - I've had a look at their website (https://www.devart.com) and they don't say that they support anything higher than SQL 2014

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • I quite like Apex's formatting freebie, it's configurable enough to get at least close to most rational formatting preferences. I've not used it with 2016, but it works fine on 2005 - 2014.

    http://www.apexsql.com/free/?gclid=CISm3JGg_80CFQbgGwodQQsMYw

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • It's good but I can't see how to get it to format BACKUP/RESTORE statements.

    I like to generate the script with the SSMS GUI, script it out and then format it (in old versions of SSMS).

    I don't think ApexSQL will do that however.

    EDIT: spelling

  • Stuart Davies (7/19/2016)


    Have you tried DBforge SQL complete?

    ETA - I've had a look at their website (https://www.devart.com) and they don't say that they support anything higher than SQL 2014

    Indeed. I raised a question with them and they said they don't have a version for SSMS 2016, but they're working on it and it should be available "in the nearest future".

    John

  • Cool! Will wait and check that out when it becomes available.

  • It's ready now!

    John

  • DBA From The Cold (7/19/2016)


    Had a look at SQL prompt, bit pricey for a formatter isn't it?

    Yes, if that was all it does, it would be a bit pricey...

    Bits of it I've used just today include the intellisense replacement, the bit that shows you the definition of an object while you're typing a reference to it, the bit that rescues old unsaved queries from its history, some code snippets and, of course, the formatting. So many times. This is a typical day. Yesterday, it warned me about running a couple of UPDATEs without WHERE clauses.

    Sorry. This is sounding like a review. No, I'm not a red gate employee, but I am a friend of red gate, which means I get to use their software without having to pay for it. But this is software that I would pay for out of my own pocket...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Thanks, will give the dbForge trial a whirl

    I'm not gonna go for the Red Gate product as even though it does have more features I really just want a code formatter (although the query history would be handy).

    EDIT: Didn't see Thomas' reply

  • Downloaded and installed first the express dbforge version and then the full trial but unfortunately it doesn't work for backup/restore statements either.

    Ah well...

  • DBA From The Cold (7/26/2016)


    Downloaded and installed first the express dbforge version and then the full trial but unfortunately it doesn't work for backup/restore statements either.

    Ah well...

    can you post some examples of the backup/restore commands you are trying to format?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • They're just standard backup/restore statements. I like to generate the statements by using the gui and then give them a once over before running.

    So from:-

    RESTORE DATABASE [TESTDB] FROM DISK = N'D:\SQLBackup\TESTDB_FULL_1.bak', DISK = N'D:\SQLBackup\TESTDB_FULL_2.bak', WITH FILE = 1, MOVE N'TESTDB' TO N'D:\SQLData\TESTDB.mdf', MOVE N'TESTDB_LoggingData1' TO N'D:\SQLData\TESTDB_LoggingData1.ndf', MOVE N'TESTDB_LoggingData2' TO N'D:\SQLData\TESTDB_LoggingData2.ndf', MOVE N'TESTDB_log' TO N'E:\SQLLog\TESTDB_log.ldf', NOUNLOAD, STATS = 5;

    To:-

    RESTORE DATABASE [TESTDB]

    FROM DISK = N'D:\SQLBackup\TESTDB_FULL_1.bak'

    ,DISK = N'D:\SQLBackup\TESTDB_FULL_2.bak'

    WITH FILE = 1

    ,MOVE N'TESTDB' TO N'D:\SQLData\TESTDB.mdf'

    ,MOVE N'TESTDB_LoggingData1' TO N'D:\SQLData\TESTDB_LoggingData1.ndf'

    ,MOVE N'TESTDB_LoggingData2' TO N'D:\SQLData\TESTDB_LoggingData2.ndf'

    ,MOVE N'TESTDB_log' TO N'E:\SQLLog\TESTDB_log.ldf'

    ,NOUNLOAD

    ,STATS = 5;

    The poor man's t-sql formatter did this really well but it's not been updated in a while and is not compatible with SSMS 2016

Viewing 15 posts - 1 through 15 (of 24 total)

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