Sunday, July 10, 2011

Storing TSQL Queries in a table without losing formatting

The Problem

The developer writes a well formatted TSQL code and stores it into the table. Later on when a change request comes, he retrieves the query text from the table and finds that all the formatting is lost.

Why does it happen?

Well, the formatting is not really lost. When the developer stores a well formatted query into the VARCHAR(MAX)/NVARCHAR(MAX) column of a table, the formatting is also stored. The problem is the way he retrieves it.

The common way of retrieving the query text is to run a SELECT query which will display the result in a grid view. The grid view does not maintain the special characters (line feed, carriage return). That is the reason why you are losing the formatting.

An immediate workaround is to change the output to text and run the SELECT query. This works if the TSQL queries stored in the table are small. If the queries are large, then it might truncate the queries.

The Workaround

Let's see a workaround that shows how to retrieve the query text without losing the formatting. To see this in action, let us start by creating a table to store the queries.

USE tempdb

    DROP TABLE BRQueries

    QueryID INT,
    QueryText VARCHAR(MAX)

Next, let us write a well formed query.

   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
    t.object_id = c.object_id

Next, Let us run a SELECT query to retrieve the query text. If we use 'result to grid', we will lose the formatting. If we use 'result to text' we will get the correctly formatted text in this case. However, if the query is longer it might get truncated. If the table has several other columns and rows, the output will be completely messed up.

A workaround is to use to use the XML function processing-instruction(). Run the following query:

        SELECT QueryText AS 'processing-instruction(q)'
        FROM BRQueries b
        WHERE b.QueryID = a.QueryID
        FOR XML PATH(''), TYPE
    ) AS Query
FROM BRQueries a

Take the result of the query into a grid either by pressing CTRL+D or from the toolbar and you will see the result as follows:

Click on the desired row and it will open up the query text with the original formatting.

   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
    t.object_id = c.object_id


Our mailing address is:
26 Executive Park Drive
Suite 2650
AtlantaGeorgia 30339

Add us to your address book

Shahzad Afzal

No comments:

Popular Posts