{"id":38,"date":"2015-09-10T21:06:33","date_gmt":"2015-09-10T20:06:33","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=38"},"modified":"2015-10-20T20:52:28","modified_gmt":"2015-10-20T19:52:28","slug":"writing-my-first-sql-post","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2015\/09\/10\/writing-my-first-sql-post\/","title":{"rendered":"Writing my first SQL Post"},"content":{"rendered":"<p>See how I configured my blog to display TSQL code like SSMS&nbsp;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.<\/p>\n<p>You can find some in the same wordpress plugins search engine, I came to one called syntaxhighlighter  (<a href=\"https:\/\/wordpress.org\/plugins\/syntaxhighlighter\/\">https:\/\/wordpress.org\/plugins\/syntaxhighlighter\/<\/a>) which I find it\u2019ll do the job.<\/p>\n<p>And it does it, ~ish. The SQL syntax is quite limited, so everything looks a bit plain. I\u2019m 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\u2026<\/p>\n<p>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\u2026 YES!<\/p>\n<p>Each language needs to be defined on its own Javascript file and it\u2019s got to be initialized.<\/p>\n<p>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\u2019s best creating a child theme, where we can have another functions.php file to complement the original theme\u2019s one.<\/p>\n<p>To create a child theme see <a href=\"https:\/\/codex.wordpress.org\/Child_Themes\">https:\/\/codex.wordpress.org\/Child_Themes<\/a><\/p>\n<p>So putting things together, we need:<br \/>\n&#8211;\tJavascript File which defines the syntax for our language<br \/>\n&#8211;\tSince the SSMS is very rich in colors, a personalize css file<br \/>\n&#8211;\tInitialize both files in functions.php<\/p>\n<p>These files are included to download <a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/09\/TSQL-Highlighter.zip\">here<\/a><\/p>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\n&lt;?php\r\n\/\/ Add custom css to support TSQL language\r\nfunction theme_enqueue_styles() {\r\n    $parent_style = 'parent-style';\r\n    wp_enqueue_style( 'custom-style', get_stylesheet_directory_uri() . '\/shTSQL.css', false);\r\n}\r\nadd_action( 'wp_enqueue_scripts', 'theme_enqueue_styles' );\r\n\r\n\/\/ SyntaxHighlighter Evolved doesn't do anything until early in the &quot;init&quot; hook, so best to wait until after that\r\nadd_action( 'init', 'syntaxhighlighter_TSQL_regscript' );\r\n\r\n\/\/ Tell SyntaxHighlighter Evolved about this new language\/brush\r\nadd_filter( 'syntaxhighlighter_brushes', 'syntaxhighlighter_TSQL_addlang' );\r\n \r\n\/\/ Register the brush file with WordPress\r\nfunction syntaxhighlighter_TSQL_regscript() {\r\n    wp_enqueue_script( 'syntaxhighlighter-brush-TSQL', get_stylesheet_directory_uri().'\/shBrushTSQL.js', array('syntaxhighlighter-core'), '20150902' );    \r\n}\r\n\/\/ Filter SyntaxHighlighter Evolved's language array\r\nfunction syntaxhighlighter_TSQL_addlang( $brushes ) {\r\n    $brushes['TSQL'] = 'TSQL';\r\n    $brushes['tsql'] = 'tsql';\r\n    return $brushes;\r\n}\r\n?&gt;\r\n<\/pre>\n<p>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.<\/p>\n<p>The list of keywords is customizable, I made it checking Books Online, SSMS system functions and more. <\/p>\n<p>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.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\n\r\nIF OBJECT_ID('tempdb..#systemReservedTypes')\tIS NOT NULL DROP TABLE #systemReservedTypes\r\nIF OBJECT_ID('tempdb..#systemReserved')\t\t\tIS NOT NULL DROP TABLE #systemReserved\r\nGO\r\n\r\nCREATE TABLE #systemReservedTypes(\r\n\ttype_desc SYSNAME COLLATE Latin1_General_CI_AS_KS_WS\r\n\t, color CHAR(7) COLLATE Latin1_General_CI_AS_KS_WS)\r\n\r\nCREATE TABLE #systemReserved(\r\n\ttype_desc SYSNAME COLLATE Latin1_General_CI_AS_KS_WS\r\n\t, name SYSNAME COLLATE Latin1_General_CI_AS_KS_WS)\r\n\r\nINSERT INTO #systemReservedTypes VALUES \r\n  (N'Aggregate'\t\t\t, '#FF00FF')\r\n, (N'Configuration'\t\t, '#FF00FF')\r\n, (N'Cursor'\t\t\t, '#FF00FF')\r\n, (N'Date_Time'\t\t\t, '#FF00FF')\r\n, (N'Logical'\t\t\t, '#FF00FF')\r\n, (N'Mathematical'\t\t, '#FF00FF')\r\n, (N'Metadata'\t\t\t, '#FF00FF')\r\n-- All Inserts in the keywords.sql file\r\n\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Aggregate', 'Avg')\r\n, ('Aggregate', 'Binary_checksum')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Configuration', 'ConnectionProperty')\r\n, ('Configuration', '@@Datefirst')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n ('Cursor', '@@Cursor_Rows')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Date_Time', 'Current_Timestamp')\r\n, ('Date_Time', 'Dateadd')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n ('Logical', 'CHOOSE')\r\n, ('Logical', 'IIF ')\r\n\r\nINSERT INTO #systemReserved VALUES \r\n ('Mathematical', 'Abs')\r\n, ('Mathematical', 'Acos')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n ('Metadata', 'Col_Length')\r\n, ('Metadata', 'Col_Name')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Other', 'App_Name')\r\n, ('Other', 'Cast')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Hierarchy', 'GetRoot')\r\n, ('Hierarchy', 'GetDescendant')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Rowset', 'Containstable')\r\n, ('Rowset', 'Freetexttable')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Security', 'Has_Dbaccess')\r\n, ('Security', 'original_db_name')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('String', 'Ascii')\r\n, ('String', 'Charindex')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('System_statistical', '@@Connections')\r\n, ('System_statistical', '@@Cpu_Busy')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('Text_Image', 'Patindex')\r\n, ('Text_Image', 'Textptr')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved VALUES \r\n  ('SET_Options', 'DATEFIRST')\r\n, ('SET_Options', 'DATEFORMAT')\r\n-- All Inserts in the keywords.sql file\r\n\r\nINSERT INTO #systemReserved \r\nSELECT 'System_Data_Types', name\r\nFROM sys.types\r\n\r\nINSERT INTO #systemReserved \r\nSELECT * FROM (VALUES \r\n-- Grey\r\n  ('TSQL_keyword_grey', 'ALL')\r\n, ('TSQL_keyword_grey', 'AND')\r\n-- All Inserts in the keywords.sql file\r\n-- Pink\r\n, ('TSQL_keyword_pink', 'COALESCE')\r\n, ('TSQL_keyword_pink', 'COLLATE')\r\n-- All Inserts in the keywords.sql file\r\n-- Blue\r\n, ('TSQL_keyword_blue', 'ADD')\r\n, ('TSQL_keyword_blue', 'ALTER')\r\n-- All Inserts in the keywords.sql file\r\n) AS t(type_desc, name)\r\nWHERE name NOT IN (SELECT name FROM #systemReserved)\r\n\r\n\r\nINSERT INTO #systemReserved \r\nSELECT type_desc\r\n\t\t, name \r\nFROM sys.all_objects AS t1\r\nWHERE type_desc NOT IN (\r\nN'DEFAULT_CONSTRAINT'\r\n, N'INTERNAL_TABLE'\r\n, N'SERVICE_QUEUE'\r\n, N'USER_TABLE'\r\n, N'PRIMARY_KEY_CONSTRAINT'\r\n)\r\nAND is_ms_shipped = 1\r\n\r\n-- Now put everything together\r\nSELECT \ttype_desc\r\n\t\t, N'.syntaxhighlighter .' + type_desc + N'{color : ' + c.color + N' !important;}' AS css\r\n\r\n\t\t, N'{ regex: new RegExp(this.getKeywords(' + type_desc + N'), ''gmi''),\tcss: ''' + type_desc + N''' },' AS js_css\r\n\r\n\t\t, N'var ' + c.type_desc + N' = ''' +\r\n\t\t STUFF((SELECT ' ' + name FROM #systemReserved AS t\r\n\t\t\t\t\tWHERE t.type_desc = c.type_desc\r\n\t\t\t\t\tORDER BY name\r\n\t\t\t\t\tFOR XML PATH('')), 1,1,'')\r\n\t\t\t+ N''';' AS js_var\r\n\r\nFROM #systemReservedTypes AS c\r\n<\/pre>\n<p>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 <\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n\/\/ Contributed by Raul Gonzalez \r\n\/\/ http:\/\/www.SQLDoubleG.com\r\n\r\nSyntaxHighlighter.brushes.TSql = function()\r\n{\r\n    \/*** Variables from SQL Script ***\/\r\n    \/\/ Complete list of functions from TSQL or in the file\r\n    var Aggregate = 'Avg'; \r\n    var AGGREGATE_FUNCTION = 'GeographyCollectionAggregate';\r\n    var CLR_SCALAR_FUNCTION = 'fn_cdc_decrement_lsn';\r\n    var CLR_STORED_PROCEDURE = 'sp_FuzzyLookupTableMaintenanceInstall';\r\n    var Configuration = '@@Datefirst @@Dbts';\r\n    var Cursor = '@@Cursor_Rows';\r\n    var Date_Time = 'Current_Timestamp';\r\n    var EXTENDED_STORED_PROCEDURE = 'sp_AddFunctionalUnitToComponent sp_audit_write';\r\n    var Hierarchy = 'GetAncestor GetDescendant';\r\n    var Logical = 'CHOOSE IIF ';\r\n    var Mathematical = 'Abs Acos Asin';\r\n    var Metadata = '@@Procid Change_Tracking_Cleanup_Version';\r\n    var Other = '@@Error @@Identity';\r\n    var Rowset = 'Containstable Freetexttable Opendatasource Openquery Openrowset Openxml';\r\n    var Rowset_Pink = 'CHANGETABLE';\r\n    var Security = 'Has_Dbaccess Is_Member ';\r\n    var SET_Options = 'ANSI_DEFAULTS ANSI_NULL_DFLT_OFF ';\r\n    var SQL_INLINE_TABLE_VALUED_FUNCTION = 'dm_cryptographic_provider_algorithms dm_cryptographic_provider_keys ';\r\n    var SQL_SCALAR_FUNCTION = 'fn_cColvEntries_80 fn_cdc_check_parameters ';\r\n    var SQL_STORED_PROCEDURE = 'sp_add_agent_parameter sp_add_agent_profile ';\r\n    var SQL_TABLE_VALUED_FUNCTION = 'fn_EnumCurrentPrincipals fn_helpdatatypemap ';\r\n    var String = 'Ascii Charindex Difference Len';\r\n    var System_Data_Types = 'bigint binary bit';\r\n    var System_statistical = '@@Connections';\r\n    var SYSTEM_TABLE = 'sysallocunits';\r\n    var Text_Image = 'Patindex Textptr Textvalid';\r\n    var TSQL_keyword_blue = 'ADD ALTER';\r\n    var TSQL_keyword_grey = 'ALL AND ANY';\r\n    var TSQL_keyword_pink = 'COLLATE CONTAINS ';\r\n    var VIEW = 'all_columns all_objects all_parameters ';\r\n\r\n\tthis.regexList = [\r\n        \/* generic colouring *\/\r\n\t{ regex: \/--(.*)$\/gm,\t\t\t\t\t\t\t\t\t\t\t\tcss: 'comments' },\t\t   \/\/ one line and multiline comments   -- comment\r\n        { regex: \/(\\\/\\*)[\\s\\S]*(\\*\\\/)\/gm,\t\t\t\t\t\t\t\t\tcss: 'comments' },\t\t   \/\/ multiline comments                \/* comment *\/\r\n        { regex: SyntaxHighlighter.regexLib.multiLineSingleQuotedString,\tcss: 'tsql_string' },\t   \/\/ single quoted strings             'string'\r\n        { regex: \/N'(?:\\\\.|[^'\\\\])*'\/gm,\t\t\t\t\t\t\t\t\tcss: 'tsql_string' },\t   \/\/ ' NVARCHAR strings                   N'unicode String'\r\n        { regex: \/@@([A-Z])\\w+\/ig,\t\t\t\t\t\t\t\t\t\t\tcss: 'Configuration' },\t   \/\/ Functions like @@SERVERNAME, @@ will no be matched in a regular expresion\r\n        { regex: \/sys\\.\\w+\/ig,\t\t\t\t\t\t\t\t\t\t\t\tcss: 'VIEW' },\t\t\t   \/\/ sys. schema\r\n        { regex: \/\\[[^\\[\\]]*\\]\/gm,\t\t\t\t\t\t\t\t\t\t\tcss: '' },\t\t\t       \/\/ [master]\r\n        { regex: \/[\\(\\)\\.\\,\\;\\!\\*\\\/\\|\\+\\-\\%\\&amp;\\=\\&lt;\\&gt;\\~]\/gm,\t\t\t\t\tcss: 'TSQL_keyword_grey' },\/\/ ( ) . , ; ! * \/ | + - % &amp; = &lt; &gt; ~\r\n      \r\n        \r\n        \r\n        \/*** Copied from SQL Script ***\/\r\n        { regex: new RegExp(this.getKeywords(Aggregate), 'gmi'),\t\t\t\t        css: 'Aggregate' },\r\n        { regex: new RegExp(this.getKeywords(AGGREGATE_FUNCTION), 'gmi'),\t\t        css: 'AGGREGATE_FUNCTION' },\r\n        { regex: new RegExp(this.getKeywords(CLR_SCALAR_FUNCTION), 'gmi'),\t\t        css: 'CLR_SCALAR_FUNCTION' },\r\n        { regex: new RegExp(this.getKeywords(CLR_STORED_PROCEDURE), 'gmi'),\t\t        css: 'CLR_STORED_PROCEDURE' },\r\n        { regex: new RegExp(this.getKeywords(Configuration), 'gmi'),\t\t\t        css: 'Configuration' },\r\n        { regex: new RegExp(this.getKeywords(Cursor), 'gmi'),\t\t\t\t            css: 'Cursor' },\r\n        { regex: new RegExp(this.getKeywords(Date_Time), 'gmi'),\t\t\t\t        css: 'Date_Time' },\r\n        { regex: new RegExp(this.getKeywords(EXTENDED_STORED_PROCEDURE), 'gmi'),        css: 'EXTENDED_STORED_PROCEDURE' },\r\n\/\/ complete list in shBrushTSQL.js\r\n\r\n\t\t];\r\n};\r\n\r\nSyntaxHighlighter.brushes.TSql.prototype\t= new SyntaxHighlighter.Highlighter();\r\nSyntaxHighlighter.brushes.TSql.aliases\t= ['tsql'];\r\n<\/pre>\n<p>And the CSS file will look something like this<\/p>\n<pre class=\"brush: css; title: ; notranslate\" title=\"\">\r\n.syntaxhighlighter .Aggregate{color : #FF00FF !important;}\r\n.syntaxhighlighter .AGGREGATE_FUNCTION{color : #000000 !important;}\r\n.syntaxhighlighter .CLR_SCALAR_FUNCTION{color : #000000 !important;}\r\n.syntaxhighlighter .CLR_STORED_PROCEDURE{color : #800000 !important;}\r\n.syntaxhighlighter .Configuration{color : #FF00FF !important;}\r\n.syntaxhighlighter .Cursor{color : #FF00FF !important;}\r\n.syntaxhighlighter .Date_Time{color : #FF00FF !important;}\r\n.syntaxhighlighter .EXTENDED_STORED_PROCEDURE{color : #800000 !important;}\r\n\/* Complete list in shTSQL.css *\/\r\n<\/pre>\n<p>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.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nSELECT type_desc\r\n\t\t, N'.syntaxhighlighter .' + type_desc + N'{color : ' + c.color + N' !important;}' AS css\r\n\r\n\t\t, N'{ regex: new RegExp(this.getKeywords(' + type_desc + N'), ''gmi''),\tcss: ''' + type_desc + N''' },' AS js_css\r\n\r\n\t\t, N'var ' + c.type_desc + N' = ''' +\r\n\t\t STUFF((SELECT ' ' + name FROM #systemReserved AS t\r\n\t\t\t\t\tWHERE t.type_desc = c.type_desc\r\n\t\t\t\t\tORDER BY name\r\n\t\t\t\t\tFOR XML PATH('')), 1,1,'')\r\n\t\t\t+ N''';' AS js_var\r\n\r\nFROM #systemReservedTypes AS c\r\n\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT type_desc\r\n\t\t, N'.syntaxhighlighter .' + type_desc + N'{color : ' + c.color + N' !important;}' AS css\r\n\r\n\t\t, N'{ regex: new RegExp(this.getKeywords(' + type_desc + N'), ''gmi''),\tcss: ''' + type_desc + N''' },' AS js_css\r\n\r\n\t\t, N'var ' + c.type_desc + N' = ''' +\r\n\t\t STUFF((SELECT ' ' + name FROM #systemReserved AS t\r\n\t\t\t\t\tWHERE t.type_desc = c.type_desc\r\n\t\t\t\t\tORDER BY name\r\n\t\t\t\t\tFOR XML PATH('')), 1,1,'')\r\n\t\t\t+ N''';' AS js_var\r\n\r\nFROM #systemReservedTypes AS c\r\n<\/pre>\n<p>Hope you guys like it and spread the word, so I can see more SQL Server blogs using this formatting.<\/p>\n<p>Download the files <a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/09\/TSQL-Highlighter.zip\">here<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>See how I configured my blog to display TSQL code like SSMS&nbsp;Before I start writing my first SQL Server post, I had to get one of those plugins where you can see&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,4],"tags":[],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/38"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=38"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/38\/revisions"}],"predecessor-version":[{"id":96,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/38\/revisions\/96"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=38"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=38"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=38"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}