The solution includes a very simple winforms Demo executable along with the Formatter class. When debugging, the formatter prepends an informational header like this example: In time, I'd expect a Dialect option will prove necessary or helpful, but I haven't seen the need yet. The first two options reflect common developer practices of formatting the SQL so that portions are easily commented out for debugging-as opposed to my original intention of making the SQL easier to read. The currently supported options and default values are: Handling the parens as part of the element found seems inelegant, but it actually works quite well-it's a natural consequence of the space-delimited-element strategy and facilitates distinguishing between parentheses which demarcate components of the statement from those which appear in a function call.įormatting options are passed as an equals sign / semi-colon delimited string. Token = (result.Right( 1) != " \t" ? Tabs( true) : " ") + " (" + Str.NewLine + Tabs() + " select" Increase tab level - select if (token.Equals( " (select", Str.IgnoreCase)) Keywords inside the CTE definition are formatted as usual, and the first " select" is recognized as requiring leading indentation as it is preceded by an opening parenthesis: The " with" keyword beginning the sample statement indicates that a CTE is being constructed and cte is set to reflect the current tabLevel-information that is required when the second " select" keyword (the reference) is to be formatted. Result.Right( 4) != Str.Repeat(Str.NewLine, 2) ? Str.NewLine : " ") + _ The problem turned out to be pretty thorny and left me with the feeling there may be a better (more formal, structured) way to tackle the problem that I'm missing. I'm sharing this in the hope that others may find it useful, but also hoping to learn a bit. I may have missed it but I couldn't find a satisfactory solution so I spent a few nights and weekends and came up with this. I wanted source we could include in the development of our own tools and utilities that I could modify to our preferences that was not dialect-specific-and, of course, it had to be free.
SQL FORMATTER CODE
In order to make their code easier for me to understand-and in the hopes of moving the group towards more standardized formatting in general, I started looking for a SQL formatter. I recently managed a group of data integration programmers writing a lot of sophisticated SQL. Order by SalesPersonID, SalesYear Background In SSMS 2008 it combines nicely with the built-in intelli-sense, effectively providing more-or-less the same base functionality as Red Gate's SQL Prompt (SQL Prompt does, of course, have extra stuff, like snippets, quick object scripting, etc).With Sales_CTE(SalesPersonID, SalesOrderID, SalesYear) as The SSMS Add-in allows you to set your own hotkey (default is Ctrl- K, Ctrl- F, to match Visual Studio), and formats the entire script or just the code you have selected/highlighted, if any. It's available for immediate/online use at, and just today graduated to "version 1.0" (it was in beta version for a few months), having just acquired support for MERGE statements, OUTPUT clauses, and other finicky stuff. Late answer, but hopefully worthwhile: The Poor Man's T-SQL Formatter is an open-source (free) T-SQL formatter with complete T-SQL batch/script support (any DDL, any DML), SSMS Plugin, command-line bulk formatter, and other options.
0 kommentar(er)
