How to preview large string

Often you need to debug dynamic SQL statement. Simplest way to do this is to print statement. Because of 8000 char limit (400 for unicode), it is hard to do in "standard way".

here is an example:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

PRINT @Very_big_string 

Last result will be 0800 - 4 chars  for digits and one for end line - char(13) (4+1)*5=4000

There are some methods to print string by UDF etc. I propose simpler method: print XML which will contain your string (xml is not limited by char, by size in SSMS settings only - default is 2MB
So here is an example for this string:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

SELECT CAST(''+@Very_big_string+'' as XML)

Here you can preview your string in XML viewer.

Disadvantage of this method is that all standard XML symbols like <,>,! etc are changed to ampersand sequences (eg > is changed to &dt;)

How to extract substrings limited with delimiters.

If you need extract from really big string small strings which are limited by delimiters, so for you can be useful this example:

DECLARE @text AS NVARCHAR (MAX) = '***[ddd]***
dfdf
fdfdfdfdfdf
***[fff]***
4545445
45454
***[ahaASSDAD]***

DFDFDF
***[SOME   TEXT
';

DECLARE @PatBegin AS VARCHAR (1000) = '***[';

DECLARE @PatEnd AS VARCHAR (1000) = ']***';

WITH   C1
AS     (SELECT 0 AS Num
        UNION ALL
        SELECT 0),
       C2
AS     (SELECT C1.Num
        FROM   C1 CROSS JOIN C1 AS C2),
       C3
AS     (SELECT C2.Num
        FROM   C2 CROSS JOIN C2 AS C3),
       C4
AS     (SELECT C3.Num
        FROM   C3 CROSS JOIN C3 AS C4),
       C5
AS     (SELECT C4.Num
        FROM   C4 CROSS JOIN C4 AS C5),
       numbers
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY o.Num) AS Number
        FROM   C5 AS o),
       counts
AS     (SELECT COUNT(*) AS CNT
        FROM   numbers
        WHERE  PATINDEX(@PatEnd, @TExt) = 1),
       c
AS     (SELECT N.Number AS CHARBegin
        FROM   numbers AS N
        --CROSS JOIN numbers N2
        WHERE  SUBSTRING(@text, N.Number, LEN(@PatBegin)) = @PatBegin),
       d
AS     (SELECT c.CHARBegin,
               T.CHARBegin AS CharEnd,
               ISNULL(c.CHARBegin + LEN(@PatBegin) + NULLIF (CHARINDEX(@PatEnd, SUBSTRING(@text, c.CHARBegin + 1, ISNULL(T.CHARBegin, 2E7) - C.CHARBegin)), 0), 2e7) AS FactEnd
        FROM   c OUTER APPLY (SELECT TOP 1 C2.CHARBegin
                              FROM   C AS C2
                              WHERE  C2.CHARBegin > C.CharBegin) AS T)
SELECT SUBSTRING(@text, d.CHARBegin + LEN(@PatBegin), d.FactEnd - [d].[CHARBegin] - LEN(@PatBegin) - LEN(@PatEnd)) AS String_OUT,
       [d].CHARBegin
FROM   d;

here you have 2 delimiter - @PatBegin and @PatEnd - begin and end delimiters.
Result here will be:

UPDATE (09-01-2012)
Fixed logic to handle lack of last closing delimiter

How to capture changed data without schema modification : Part 2

In previous part I showed how to store changed rows. Now we will store changes in database in separate table 

Main idea how to get columns names is to use XML: at first, we create an XML from joined together old and new rows, and then we extract both old and new value as well as column name and write this data to audit table. To detect NULL value, we should use XSINIL argument of FOR XML clause.

Now we have in our audit table all necessary information: id, changed column name and both new and old values.

Conclusion

In this two-part article I tried to explain how is possible to capture data change even without data scheme modification. You can download full script from link below and change tables names to real. If you have any useful suggestion, please tell me.


How_to_capture_changed_columns.sql (4Kb)

How to capture changed data without schema modification : Part 1

Sometimes we need to capture changed row in table.  One good approach is to use change tracking option which is available in SQL Server 2008 and later. Another good solution is to use triggers on tables which linking deleted and inserted tables. But what if we have no permissions to modify database schema?

In that case we can use OUTPUT clause in INSERT OR UPDATE statement.

Here is an short example.

Let us create two table variables: @t_original  will be original (changed) table and @t_copy which will store deleted row.

Then we insert two rows into originl table. Changed rows will be inserted into table @t_copy

UPDATE  @t_original
SET     AnotherColumn = 4 ,
   Name = NULL
OUTPUT  DELETED.*
        INTO @t_copy
WHERE   id = 1 ;

Now we have both old and new rows and can use it later to compare and check differences.

SELECT  Copy.id ,
        Copy.NAME Name_Old ,
        Copy.AnotherColumn AnotherColumn_Old ,
        Original.NAME Name_New ,
        Original.AnotherColumn AnotherColumn_New
FROM    @t_copy Copy
        RIGHT OUTER JOIN @t_original Original ON Copy.id = Original.id

We will use results from those statements later to answer a question which columns were changed.