Before I start writing my first SQL Server post, I had to get one of those plugins where you can see your code as you see it in SSMS.
You can find some in the same wordpress plugins search engine, I came to one called syntaxhighlighter (https://wordpress.org/plugins/syntaxhighlighter/) which I find it’ll do the job.
And it does it, ~ish. The SQL syntax is quite limited, so everything looks a bit plain. I’m used to the colouring scheme SSMS has and I wanted my blog posts to look like it, I think it makes reading queries easier, so I tried to get a proper TSQL syntax, with no success. So I must create my own if I wanted to have it my way…
Thankfully the author of the plugin, a guy called Alex Gorbatchev, was nice enough to explain on this website how you can create you custom language… YES!
Each language needs to be defined on its own Javascript file and it’s got to be initialized.
To avoid your language being overwritten in case you upgrade the plugin is recommended to place it somewhere else, so considering we have to initialize it in /out-theme/functions.php, it’s best creating a child theme, where we can have another functions.php file to complement the original theme’s one.
To create a child theme see https://codex.wordpress.org/Child_Themes
So putting things together, we need:
– Javascript File which defines the syntax for our language
– Since the SSMS is very rich in colors, a personalize css file
– Initialize both files in functions.php
These files are included to download here
<?php
// Add custom css to support TSQL language
function theme_enqueue_styles() {
$parent_style = 'parent-style';
wp_enqueue_style( 'custom-style', get_stylesheet_directory_uri() . '/shTSQL.css', false);
}
add_action( 'wp_enqueue_scripts', 'theme_enqueue_styles' );
// SyntaxHighlighter Evolved doesn't do anything until early in the "init" hook, so best to wait until after that
add_action( 'init', 'syntaxhighlighter_TSQL_regscript' );
// Tell SyntaxHighlighter Evolved about this new language/brush
add_filter( 'syntaxhighlighter_brushes', 'syntaxhighlighter_TSQL_addlang' );
// Register the brush file with WordPress
function syntaxhighlighter_TSQL_regscript() {
wp_enqueue_script( 'syntaxhighlighter-brush-TSQL', get_stylesheet_directory_uri().'/shBrushTSQL.js', array('syntaxhighlighter-core'), '20150902' );
}
// Filter SyntaxHighlighter Evolved's language array
function syntaxhighlighter_TSQL_addlang( $brushes ) {
$brushes['TSQL'] = 'TSQL';
$brushes['tsql'] = 'tsql';
return $brushes;
}
?>
So we need to define both the JavaScript file and the CSS, but being this a SQL Server blog, I wanted to use TSQL to generate the code and make the process a bit less copy paste.
The list of keywords is customizable, I made it checking Books Online, SSMS system functions and more.
The script consists in 2 tables where I defined the keyword type and the color (as in SSMS) and the list of keywords for that type. You can modify it to your taste if you want to.
IF OBJECT_ID('tempdb..#systemReservedTypes') IS NOT NULL DROP TABLE #systemReservedTypes
IF OBJECT_ID('tempdb..#systemReserved') IS NOT NULL DROP TABLE #systemReserved
GO
CREATE TABLE #systemReservedTypes(
type_desc SYSNAME COLLATE Latin1_General_CI_AS_KS_WS
, color CHAR(7) COLLATE Latin1_General_CI_AS_KS_WS)
CREATE TABLE #systemReserved(
type_desc SYSNAME COLLATE Latin1_General_CI_AS_KS_WS
, name SYSNAME COLLATE Latin1_General_CI_AS_KS_WS)
INSERT INTO #systemReservedTypes VALUES
(N'Aggregate' , '#FF00FF')
, (N'Configuration' , '#FF00FF')
, (N'Cursor' , '#FF00FF')
, (N'Date_Time' , '#FF00FF')
, (N'Logical' , '#FF00FF')
, (N'Mathematical' , '#FF00FF')
, (N'Metadata' , '#FF00FF')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Aggregate', 'Avg')
, ('Aggregate', 'Binary_checksum')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Configuration', 'ConnectionProperty')
, ('Configuration', '@@Datefirst')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Cursor', '@@Cursor_Rows')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Date_Time', 'Current_Timestamp')
, ('Date_Time', 'Dateadd')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Logical', 'CHOOSE')
, ('Logical', 'IIF ')
INSERT INTO #systemReserved VALUES
('Mathematical', 'Abs')
, ('Mathematical', 'Acos')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Metadata', 'Col_Length')
, ('Metadata', 'Col_Name')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Other', 'App_Name')
, ('Other', 'Cast')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Hierarchy', 'GetRoot')
, ('Hierarchy', 'GetDescendant')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Rowset', 'Containstable')
, ('Rowset', 'Freetexttable')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Security', 'Has_Dbaccess')
, ('Security', 'original_db_name')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('String', 'Ascii')
, ('String', 'Charindex')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('System_statistical', '@@Connections')
, ('System_statistical', '@@Cpu_Busy')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('Text_Image', 'Patindex')
, ('Text_Image', 'Textptr')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved VALUES
('SET_Options', 'DATEFIRST')
, ('SET_Options', 'DATEFORMAT')
-- All Inserts in the keywords.sql file
INSERT INTO #systemReserved
SELECT 'System_Data_Types', name
FROM sys.types
INSERT INTO #systemReserved
SELECT * FROM (VALUES
-- Grey
('TSQL_keyword_grey', 'ALL')
, ('TSQL_keyword_grey', 'AND')
-- All Inserts in the keywords.sql file
-- Pink
, ('TSQL_keyword_pink', 'COALESCE')
, ('TSQL_keyword_pink', 'COLLATE')
-- All Inserts in the keywords.sql file
-- Blue
, ('TSQL_keyword_blue', 'ADD')
, ('TSQL_keyword_blue', 'ALTER')
-- All Inserts in the keywords.sql file
) AS t(type_desc, name)
WHERE name NOT IN (SELECT name FROM #systemReserved)
INSERT INTO #systemReserved
SELECT type_desc
, name
FROM sys.all_objects AS t1
WHERE type_desc NOT IN (
N'DEFAULT_CONSTRAINT'
, N'INTERNAL_TABLE'
, N'SERVICE_QUEUE'
, N'USER_TABLE'
, N'PRIMARY_KEY_CONSTRAINT'
)
AND is_ms_shipped = 1
-- Now put everything together
SELECT type_desc
, N'.syntaxhighlighter .' + type_desc + N'{color : ' + c.color + N' !important;}' AS css
, N'{ regex: new RegExp(this.getKeywords(' + type_desc + N'), ''gmi''), css: ''' + type_desc + N''' },' AS js_css
, N'var ' + c.type_desc + N' = ''' +
STUFF((SELECT ' ' + name FROM #systemReserved AS t
WHERE t.type_desc = c.type_desc
ORDER BY name
FOR XML PATH('')), 1,1,'')
+ N''';' AS js_var
FROM #systemReservedTypes AS c
So, executing the TSQL above, you will get the variable part of the code you need for your custom JavaScript and the CSS files, that would be like
// Contributed by Raul Gonzalez
// http://www.SQLDoubleG.com
SyntaxHighlighter.brushes.TSql = function()
{
/*** Variables from SQL Script ***/
// Complete list of functions from TSQL or in the file
var Aggregate = 'Avg';
var AGGREGATE_FUNCTION = 'GeographyCollectionAggregate';
var CLR_SCALAR_FUNCTION = 'fn_cdc_decrement_lsn';
var CLR_STORED_PROCEDURE = 'sp_FuzzyLookupTableMaintenanceInstall';
var Configuration = '@@Datefirst @@Dbts';
var Cursor = '@@Cursor_Rows';
var Date_Time = 'Current_Timestamp';
var EXTENDED_STORED_PROCEDURE = 'sp_AddFunctionalUnitToComponent sp_audit_write';
var Hierarchy = 'GetAncestor GetDescendant';
var Logical = 'CHOOSE IIF ';
var Mathematical = 'Abs Acos Asin';
var Metadata = '@@Procid Change_Tracking_Cleanup_Version';
var Other = '@@Error @@Identity';
var Rowset = 'Containstable Freetexttable Opendatasource Openquery Openrowset Openxml';
var Rowset_Pink = 'CHANGETABLE';
var Security = 'Has_Dbaccess Is_Member ';
var SET_Options = 'ANSI_DEFAULTS ANSI_NULL_DFLT_OFF ';
var SQL_INLINE_TABLE_VALUED_FUNCTION = 'dm_cryptographic_provider_algorithms dm_cryptographic_provider_keys ';
var SQL_SCALAR_FUNCTION = 'fn_cColvEntries_80 fn_cdc_check_parameters ';
var SQL_STORED_PROCEDURE = 'sp_add_agent_parameter sp_add_agent_profile ';
var SQL_TABLE_VALUED_FUNCTION = 'fn_EnumCurrentPrincipals fn_helpdatatypemap ';
var String = 'Ascii Charindex Difference Len';
var System_Data_Types = 'bigint binary bit';
var System_statistical = '@@Connections';
var SYSTEM_TABLE = 'sysallocunits';
var Text_Image = 'Patindex Textptr Textvalid';
var TSQL_keyword_blue = 'ADD ALTER';
var TSQL_keyword_grey = 'ALL AND ANY';
var TSQL_keyword_pink = 'COLLATE CONTAINS ';
var VIEW = 'all_columns all_objects all_parameters ';
this.regexList = [
/* generic colouring */
{ regex: /--(.*)$/gm, css: 'comments' }, // one line and multiline comments -- comment
{ regex: /(\/\*)[\s\S]*(\*\/)/gm, css: 'comments' }, // multiline comments /* comment */
{ regex: SyntaxHighlighter.regexLib.multiLineSingleQuotedString, css: 'tsql_string' }, // single quoted strings 'string'
{ regex: /N'(?:\\.|[^'\\])*'/gm, css: 'tsql_string' }, // ' NVARCHAR strings N'unicode String'
{ regex: /@@([A-Z])\w+/ig, css: 'Configuration' }, // Functions like @@SERVERNAME, @@ will no be matched in a regular expresion
{ regex: /sys\.\w+/ig, css: 'VIEW' }, // sys. schema
{ regex: /\[[^\[\]]*\]/gm, css: '' }, // [master]
{ regex: /[\(\)\.\,\;\!\*\/\|\+\-\%\&\=\<\>\~]/gm, css: 'TSQL_keyword_grey' },// ( ) . , ; ! * / | + - % & = < > ~
/*** Copied from SQL Script ***/
{ regex: new RegExp(this.getKeywords(Aggregate), 'gmi'), css: 'Aggregate' },
{ regex: new RegExp(this.getKeywords(AGGREGATE_FUNCTION), 'gmi'), css: 'AGGREGATE_FUNCTION' },
{ regex: new RegExp(this.getKeywords(CLR_SCALAR_FUNCTION), 'gmi'), css: 'CLR_SCALAR_FUNCTION' },
{ regex: new RegExp(this.getKeywords(CLR_STORED_PROCEDURE), 'gmi'), css: 'CLR_STORED_PROCEDURE' },
{ regex: new RegExp(this.getKeywords(Configuration), 'gmi'), css: 'Configuration' },
{ regex: new RegExp(this.getKeywords(Cursor), 'gmi'), css: 'Cursor' },
{ regex: new RegExp(this.getKeywords(Date_Time), 'gmi'), css: 'Date_Time' },
{ regex: new RegExp(this.getKeywords(EXTENDED_STORED_PROCEDURE), 'gmi'), css: 'EXTENDED_STORED_PROCEDURE' },
// complete list in shBrushTSQL.js
];
};
SyntaxHighlighter.brushes.TSql.prototype = new SyntaxHighlighter.Highlighter();
SyntaxHighlighter.brushes.TSql.aliases = ['tsql'];
And the CSS file will look something like this
.syntaxhighlighter .Aggregate{color : #FF00FF !important;}
.syntaxhighlighter .AGGREGATE_FUNCTION{color : #000000 !important;}
.syntaxhighlighter .CLR_SCALAR_FUNCTION{color : #000000 !important;}
.syntaxhighlighter .CLR_STORED_PROCEDURE{color : #800000 !important;}
.syntaxhighlighter .Configuration{color : #FF00FF !important;}
.syntaxhighlighter .Cursor{color : #FF00FF !important;}
.syntaxhighlighter .Date_Time{color : #FF00FF !important;}
.syntaxhighlighter .EXTENDED_STORED_PROCEDURE{color : #800000 !important;}
/* Complete list in shTSQL.css */
Now you can see in the difference between formatting TSQL to my custom style and the SQL definition which comes with the plugin is huge.
SELECT type_desc
, N'.syntaxhighlighter .' + type_desc + N'{color : ' + c.color + N' !important;}' AS css
, N'{ regex: new RegExp(this.getKeywords(' + type_desc + N'), ''gmi''), css: ''' + type_desc + N''' },' AS js_css
, N'var ' + c.type_desc + N' = ''' +
STUFF((SELECT ' ' + name FROM #systemReserved AS t
WHERE t.type_desc = c.type_desc
ORDER BY name
FOR XML PATH('')), 1,1,'')
+ N''';' AS js_var
FROM #systemReservedTypes AS c
SELECT type_desc
, N'.syntaxhighlighter .' + type_desc + N'{color : ' + c.color + N' !important;}' AS css
, N'{ regex: new RegExp(this.getKeywords(' + type_desc + N'), ''gmi''), css: ''' + type_desc + N''' },' AS js_css
, N'var ' + c.type_desc + N' = ''' +
STUFF((SELECT ' ' + name FROM #systemReserved AS t
WHERE t.type_desc = c.type_desc
ORDER BY name
FOR XML PATH('')), 1,1,'')
+ N''';' AS js_var
FROM #systemReservedTypes AS c
Hope you guys like it and spread the word, so I can see more SQL Server blogs using this formatting.
Download the files here
What a good article! This is really useful for me. Thanks!