I wrote the prettifier specifically to help with posting SQL into blogs and forums. You can find it here http://extras.sqlservercentral.com/prettifier/prettifier.aspx Actually, it is just a stored procedure that does all the work. The website has very little progamming logic in it. (I haven't had a moment to update the prettifier to SQL Server 2008. Soon, soon)
The most common problem is posting code into forums. The SQL Server Central forum uses IFCodes, which are a variant of the old BBCodes. These are delimited by square brackets ([]). You can ask the prettifier to render your code using these IFcodes. You just click the radio-button that is marked 'IFcodes (e.g. SSC forum)', then hit the 'Prettify' button, and finally copy the results from the Source HTML pane and paste it into the source of your forum contribution, having admired what it will look like in the Rendered HTML pane. This allows you to either put formatted TSQL code into the body of your forum entry or put it in a section so that it gets a grey background and a scroll bar.
--so the code...
ALTER view [dbo].[Alphabetical list of products] AS
SELECT Products.*, Categories.CategoryName
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))
--becomes
[font="Courier New"][color="blue"]ALTER VIEW [/color][color="black"][dbo].[Alphabetical list of products] [/color][color="blue"]AS
SELECT [/color][color="black"]Products.[/color][color="gray"]*, [/color][color="black"]Categories.CategoryName
    [/color][color="blue"]FROM [/color][color="black"]Categories
    [/color][color="blue"]INNER JOIN [/color][color="black"]Products [/color][color="blue"]ON [/color][color="black"]Categories.CategoryID [/color][color="blue"]= [/color][color="black"]Products.CategoryID
    [/color][color="blue"]WHERE [/color][color="gray"]((([/color][color="black"]Products.Discontinued[/color][color="gray"])[/color][color="blue"]=[/color][color="black"]0[/color][color="gray"]))
[/color][/font]
but this is grist to the SSC mill. It will come out nicely rendered. If you just intend to add forum entries, that is all all you'll need to know.
If you are writing a blog into most Blogging systems, you are going to hit problems, but actual problems will vary according to the Blogging Software used. Community Server has no means to display code in a format that is exactly like the representation in the code window of SSMS or query analyser. It uses the tiny MCE editor which is developed as a generic text editor for IE. Because blogging software packages have to be generic, they are geared to ordinary blogs about your life and stamp collection, or whatever. Community Server, typically, shows several linked problems. When you paste in code, only the colour information is transferred. It would look like this.
ALTER view [dbo].[Alphabetical list of products] ASSELECT
Products.*, Categories.CategoryNameFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE (((Products.Discontinued)=0))Hmm.. Wrong font, wrong size, Lost all the indenting. Wide spacing between lines. Otherwise fine.
Lines have the default spacing that the browser gives the <p> tag. This means that you are left to put in <BR> characters instead. There is nothing that will help you do this. To make matters worse, the spacing that is used by any code editor to format code nicely is meaningless to HTML so you have to give up and put all your code between <pre> tags.
The Prettifier has a mode for coping with this sort of tiresomeness. Just click on the 'Forums' radiobutton on the left-hand pane and the source HTML output will be
<pre style="font-size: 12px;"><font color="blue">ALTER VIEW </font><font color="black">[dbo].[Alphabetical list of products] </font><font color="blue">AS<br>SELECT </font><font color="black">Products.</font><font color="gray">*, </font><font color="black">Categories.CategoryName<br> </font><font color="blue">FROM </font><font color="black">Categories <br> </font><font color="blue">INNER JOIN </font><font color="black">Products </font><font color="blue">ON </font><font color="black">Categories.CategoryID </font><font color="blue">= </font><font color="black">Products.CategoryID <br> </font><font color="blue">WHERE </font><font color="gray">(((</font><font color="black">Products.Discontinued</font><font color="gray">)</font><font color="blue">=</font><font color="black">0</font><font color="gray">))<br><br></font></pre>
you have to paste this into the HTML version of your blog to produce this version...
ALTER VIEW [dbo].[Alphabetical list of products] AS
SELECT Products.*, Categories.CategoryName
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))
There is a slight awkwardness about this as it will often insist on a certain width of its enclosing tag. This can result in it messing with the page layout rather than wrapping around. A better solution is to change the bounding tags (<pre style="font-size: 12px;"></pre) with (<font face="courier new" size="2"></font>)
If, of course, you are putting code into your own website, or some other place where you have better control of the style-sheets or in-line style blocks, then you have more options.
I like the XHTML version. Our code sample would become
<div class="listing"><span class="codeBlue">ALTER VIEW </span><span class="codeBlack">[dbo].[Alphabetical list of products] </span><span class="codeBlue">AS<br />SELECT </span><span class="codeBlack">Products.</span><span class="codeGray">*, </span><span class="codeBlack">Categories.CategoryName<br />    </span><span class="codeBlue">FROM </span><span class="codeBlack">Categories <br />    </span><span class="codeBlue">INNER JOIN </span><span class="codeBlack">Products </span><span class="codeBlue">ON </span><span class="codeBlack">Categories.CategoryID </span><span class="codeBlue">= </span><span class="codeBlack">Products.CategoryID<br />    </span><span class="codeBlue">WHERE </span><span class="codeGray">(((</span><span class="codeBlack">Products.Discontinued</span><span class="codeGray">)</span><span class="codeBlue">=</span><span class="codeBlack">0</span><span class="codeGray">))<br /><br /><br /></span></div>
You'll notice that we've changed the default <pre> to a <div> because we don't care about wrap-around. Of course, you'd need a style-block like this beforehand
<style type="text/css">
<!--
div.listing span.codeBlack{color:black}
div.listing span.codeBlue{color:blue}
div.listing span.codeBrown{color:brown}
div.listing span.codeGray{color:gray}
div.listing span.codeGreen{color:green}
div.listing span.codeMagenta{color:magenta}
div.listing span.codeRed{color:red}
div.listing{
background:#f2f2f2;
border-bottom:#cacaca 1px solid;
border-left:#cacaca 1px solid;
border-right:#cacaca 1px solid;
border-top:#cacaca 1px solid;
font:normal 12px "Courier New",Courier,monospace;
margin-left:20px;
padding:10px;
text-align:left
}
div.listing p{
font-family:"Courier New",Courier,monospace;
margin-bottom:0px;
margin-top:0px;
text-align:left
}
-->
</style>
..Of course, this is just an example which you would alter to taste using an editor such as the excellent 'Topstyle'.
This is generally the form that Tony and Andrew use on Simple-Talk. It is especially useful since now we can leave in the <p> </p> tags as they are now properly rendered for code. Sometimes this comes in mighty handy as, for example, when they had to render some Powershell code nicely so as to make it easier to understand for the readers. (the prettifier doesn't do Powershell yet!). It was a simple routine of....
- copy the raw code into PowerGUI
- Paste the formatted code from PowerGUI into Word.
- Paste from Word into FrontPage(if you go direct from PowerGUI into Frontpage you lose the indenting.
- Cut out all the inline style information, leaving just the colour styles
- put the code between a <div class="listing">....</div>
