Having created a rudimentary audit of DDL changes to SQL Server, I wanted to be able to quickly look at the changes through SSMS. To do this, I added a custom report on the object explorer right click menu that just queryed the audit data against the particular function/procedure etc. This just makes it very quick and easy to see what has changed and when (particularly if you don’t have source control on your SQL Server Database!)
That was all easy enough, but then to make those long procedures a bit easier to read I thought it would be good to get some code highlighting involved. I can’t install a custom assembly on the server, so I had to find another way. Fortunately, this is relatively simple(ish!) using a placeholder, inserting a text object, and creating a code element in the report to pass the TSQL to for formatting.
I’m sure there is room for improvement (I particularly don’t like removing then re-adding the code comments, there must be nice regex way to do this!), but it seems to do the job OK so it’s a good start!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
Dim codeComments As New System.Collections.Generic.Dictionary(Of String, String) Dim tabReplace As String = " " Public Function HighlightSQL(ByVal sql As String) As String Dim keyWordsRegEx As New System.Text.RegularExpressions.Regex("\b(CREATE|ALTER|BEGIN|END|DECLARE|SET|ALL|DATABASE|TABLE|GRANT|PRIVILEGES|IDENTIFIED|FLUSH|SELECT|UPDATE|DELETE|INSERT|FROM|WHERE|ORDER|BY|GROUP|LIMIT|INNER|OUTER|AS|ON|COUNT|CASE|TO|IF|WHEN|BETWEEN|AND|OR|RETURNS|RETURN|BEGIN|END|EXEC|UNION)\b", System.Text.RegularExpressions.RegexOptions.Compiled Or System.Text.RegularExpressions.RegexOptions.IgnoreCase) Dim DataTypesRegEx As New System.Text.RegularExpressions.Regex("\b(BIGINT|INT|SMALLINT|TINYINT|BIT|DECIMAL|NUMERIC|MONEY|SMALLMONEY|FLOAT|REAL|DATETIME|SMALLDATETIME|DATE|TIME|DATETIME2|DATETIMEOFFSET|CHAR|VARCHAR|TEXT|NCHAR|NVARCHAR|NTEXT|BINARY|VARBINARY|IMAGE|SQL_VARIANT|TIMESTAMP|UNIQUEIDENTIFIER|XML|CURSOR|TABLE)\b", System.Text.RegularExpressions.RegexOptions.Compiled Or System.Text.RegularExpressions.RegexOptions.IgnoreCase) Dim systemFunctionsRegEx As New System.Text.RegularExpressions.Regex("\b(ASCII|LTRIM|SOUNDEX|CHAR|NCHAR|SPACE|CHARINDEX|PATINDEX|STR|CONCAT|QUOTENAME|STUFF|DIFFERENCE|REPLACE|SUBSTRING|FORMAT|REPLICATE|UNICODE|LEFT|REVERSE|UPPER|LEN|RIGHT|LOWER|RTRIM|PATINDEX|TEXTVALID|TEXTPTR)", System.Text.RegularExpressions.RegexOptions.Compiled Or System.Text.RegularExpressions.RegexOptions.IgnoreCase) Dim variablesRegEx As New System.Text.RegularExpressions.Regex("\@\w+", System.Text.RegularExpressions.RegexOptions.Compiled) Dim tempTableRegEx As New System.Text.RegularExpressions.Regex("\#\w+", System.Text.RegularExpressions.RegexOptions.Compiled) Dim lineCommentsRegEx As New System.Text.RegularExpressions.Regex("\--.*", System.Text.RegularExpressions.RegexOptions.Compiled Or System.Text.RegularExpressions.RegexOptions.Multiline) Dim longCommentsRegEx As New System.Text.RegularExpressions.Regex("\/\*(.*?)\*\/", System.Text.RegularExpressions.RegexOptions.Compiled Or System.Text.RegularExpressions.RegexOptions.Singleline Or System.Text.RegularExpressions.RegexOptions.Multiline) Dim whiteSpaceRegEx As New System.Text.RegularExpressions.Regex("\s+(?![^<>]*>)", System.Text.RegularExpressions.RegexOptions.Compiled) Dim textRegEx As New System.Text.RegularExpressions.Regex("([""'])(?:(?=(\\?))\2.)*?\1", System.Text.RegularExpressions.RegexOptions.Compiled) ' (["'])(?:(?=(\\?))\2.)*?\1 without escape character sql = longCommentsRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf RemoveComments)) sql = lineCommentsRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf RemoveComments)) 'Highlight text sql = textRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf RedHighlighter)) 'only apply the highlight to stuff not in a code comment sql = keyWordsRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf BlueHighlighter)) sql = DataTypesRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf BlueHighlighter)) sql = systemFunctionsRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf PurpleHighlighter)) 'Highlight variables and temp tables sql = variablesRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf TealHighlighter)) sql = tempTableRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf TealHighlighter)) ' Add comments back in sql = System.Text.RegularExpressions.Regex.Replace(sql, "\[Comment(.*)\]", New System.Text.RegularExpressions.MatchEvaluator(AddressOf ReplaceComments)) ' Formatting clean up ' Line Breaks sql = sql.Replace(Environment.NewLine, "<br>") sql = sql.Replace(vbNewLine, "<br>") sql = sql.Replace(vbLf, "<br>") ' Tabs sql = sql.Replace(vbTab, tabReplace) sql = sql.Replace(vbCrLf, tabReplace) ' Any remaining white space sql = whiteSpaceRegEx.Replace(sql, New System.Text.RegularExpressions.MatchEvaluator(AddressOf ReplaceWhiteSpace)) Return sql End Function Function RemoveComments(ByVal match As System.Text.RegularExpressions.Match) As String Dim index As Int16, key As String index = codeComments.Count key = "[Comment" + index.ToString() + "]" codeComments.Add(key, match.ToString()) Return key End Function Function ReplaceComments(ByVal match As System.Text.RegularExpressions.Match) As String Return "<span style='color:green'>" + codeComments.Item(match.ToString().Trim()) + "</span>" End Function Function ReplaceWhiteSpace(ByVal match As System.Text.RegularExpressions.Match) As String Return match.ToString().Replace(" ", " ") End Function Function Highlight(ByVal text As String, ByVal color As String) Return "<span style='color:" + color + "'>" + text + "</span>" End Function Function TealHighlighter(ByVal match As System.Text.RegularExpressions.Match) As String Return Highlight(match.ToString(), "teal") End Function Function BlueHighlighter(ByVal match As System.Text.RegularExpressions.Match) As String Return Highlight(match.ToString(), "blue") End Function Function RedHighlighter(ByVal match As System.Text.RegularExpressions.Match) As String Return Highlight(match.ToString(), "red") End Function Function PurpleHighlighter(ByVal match As System.Text.RegularExpressions.Match) As String Return Highlight(match.ToString(), "purple") End Function Function GreenHighlighter(ByVal match As System.Text.RegularExpressions.Match) As String Return Highlight(match.ToString(), "green") End Function |
