/**

<summary>

Based on ufn_VarbinaryToVarcharHex by Clay Beatty.

Used by Tool_ScriptDiagram2005

Function has two 'parts':

PART ONE: takes large VarbinaryValue chunks (greater than four bytes)

and splits them into half, calling the function recursively with

each half until the chunks are only four bytes long

PART TWO: notices the VarbinaryValue is four bytes or less, and

starts actually processing these four byte chunks. It does this

by splitting the least-significant (rightmost) byte into two

hexadecimal characters and recursively calling the function

with the more significant bytes until none remain (four recursive

calls in total).

</summary>

<author>Craig Dunn/Christian Coppes</author>

<remarks>

Clay Beatty's original function was written for Sql Server 2000.

Sql Server 2005 introduces the VARBINARY(max) datatype which this

function now uses.

This slightly changed version outputs the binary field as text.

References

----------

1) MSDN: Using Large-Value Data Types

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

2) Clay's "original" Script, Save, Export SQL 2000 Database Diagrams

http://www.thescripts.com/forum/thread81534.html or

http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/ca9a9229d06a56f9?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&prev=/groups%3Fdq%3D%26num%3D25%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.ms-sqlserver%26start%3D25

</remarks>

<param name="VarbinaryValue">binary data to be converted to Hexadecimal </param>

<returns>Hexadecimal representation of binary data, using chars [0-0a-f]</returns>

*/

ALTER FUNCTION [dbo].[Tool_VarbinaryToVarchar_Text]

(

    @VarbinaryValue    VARBINARY(max),

    @bitASCIIOnly    BIT = 0

)

RETURNS VARCHAR(max) AS

    BEGIN

    DECLARE @NumberOfBytes     INT

    SET @NumberOfBytes = DATALENGTH(@VarbinaryValue)

    -- PART ONE --

    IF (@NumberOfBytes > 4)

    BEGIN

        DECLARE @FirstHalfNumberOfBytes INT

        DECLARE @SecondHalfNumberOfBytes INT

        SET @FirstHalfNumberOfBytes  = @NumberOfBytes/2

        SET @SecondHalfNumberOfBytes = @NumberOfBytes - @FirstHalfNumberOfBytes

        -- Call this function recursively with the two parts of the input split in half

        RETURN dbo.Tool_VarbinaryToVarchar_Text(CAST(SUBSTRING(@VarbinaryValue, 1                            , @FirstHalfNumberOfBytes)  AS VARBINARY(max)),@bitASCIIOnly)

             + dbo.Tool_VarbinaryToVarchar_Text(CAST(SUBSTRING(@VarbinaryValue, @FirstHalfNumberOfBytes+1 , @SecondHalfNumberOfBytes) AS VARBINARY(max)),@bitASCIIOnly)

    END

    IF (@NumberOfBytes = 0)

    BEGIN

        RETURN ''    -- No bytes found, therefore no 'hex string' is returned

    END

    -- PART TWO --

    DECLARE @HighByte         INT

    -- @NumberOfBytes <= 4 (four or less characters/8 hex digits were input)

    --                         eg. 88887777 66665555 44443333 22221111

    -- We'll process ONLY the right-most (least-significant) Byte, which consists

    -- of eight bits

    -- 2. Carve off the rightmost eight bits/single hex digit (ie 22221111)

    --    Divide by 16 does a shift-left (now processing 2222)

    SET @HighByte = CAST(@VarbinaryValue AS INT) & 255

    IF @bitASCIIOnly = 1 AND (@HighByte < 32 OR @HighByte > 126) SET @HighByte=13;

    -- 3. Trim the byte (two hex values) from the right (least significant) input Binary

    --    in preparation for further parsing

    SET @VarbinaryValue = SUBSTRING(@VarbinaryValue, 1, (@NumberOfBytes-1))

    -- 4. Recursively call this method on the remaining Binary data, concatenating the text

    --    'value' we just decoded as their ASCII character representation

    --    ie. we pass 88887777 66665555 44443333 back to this function, adding X to the result string

    RETURN dbo.Tool_VarbinaryToVarchar_Text(@VarbinaryValue,@bitASCIIOnly) + CHAR(@HighByte)

END

/**

<summary>

Script Sql Server 2005 diagrams

(inspired by usp_ScriptDatabaseDiagrams for Sql Server 2000 by Clay Beatty)

</summary>

<example>

--NOTE: Scalar-valued Function [Tool_VarbinaryToVarchar_Text] must exist before this script is run

SELECT * FROM [dbo].[fnTool_ScriptDiagram2005_Text] () WHERE diagram_ASCII LIKE '%tblUser%'

(Lists all diagrams which contains "tblUser")

</example>

<author>Craig Dunn</author>

<remarks>

Helpful Articles

----------------

1) Upload / Download to Sql 2005

http://staceyw.spaces.live.com/blog/cns!F4A38E96E598161E!404.entry

2) MSDN: Using Large-Value Data Types

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

3) "original" Script, Save, Export SQL 2000 Database Diagrams

http://www.thescripts.com/forum/thread81534.html

http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/ca9a9229d06a56f9?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&prev=/groups%3Fdq%3D%26num%3D25%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.ms-sqlserver%26start%3D25

</remarks>

<param name="name">Name of the diagram in the Sql Server database instance</param>

*/

CREATE FUNCTION [dbo].[fnTool_ScriptDiagram2005_Text]()

RETURNS

@tblOut TABLE

(

    -- Add the column definitions for the TABLE variable here

    diagramname        NVARCHAR(128),

    diagram_id        INT PRIMARY KEY,

    diagram_text    VARCHAR(MAX),

    diagram_ASCII    VARCHAR(MAX)

)

AS

BEGIN

    DECLARE @name            NVARCHAR(128);

    DECLARE @diagram_id        INT;

    DECLARE @index            INT;

    DECLARE @size            INT;

    DECLARE @chunk            INT;

    DECLARE @line            VARCHAR(MAX);

    DECLARE @lineASC        VARCHAR(MAX);

    DECLARE @CurrentPos        INT;

    SELECT @CurrentPos = MIN(diagram_id) FROM dbo.sysdiagrams;

    WHILE (@CurrentPos IS NOT NULL)

    BEGIN

        -- Set start index, and chunk 'constant' value

        SET @index = 1;        --

        SET @chunk = 32;    -- values that work: 2, 6

                            -- values that fail: 15,16, 64

        SELECT    @diagram_id = diagram_id,

                @size = DATALENGTH(definition),

                @name = name

          FROM dbo.sysdiagrams

         WHERE diagram_id = @CurrentPos;

        -- Now with the diagram_id, do all the work

        SET @line = '';

        SET @lineASC = '';

        WHILE @index < @size

        BEGIN

            -- Output as many UPDATE statements as required to append all the diagram binary

            -- data, represented as hexadecimal strings

            SELECT  @line = @line + dbo.Tool_VarbinaryToVarchar_Text(SUBSTRING (definition, @index, @chunk),0),

                    @lineASC = @lineASC + dbo.Tool_VarbinaryToVarchar_Text(SUBSTRING (definition, @index, @chunk),1)

              FROM    dbo.sysdiagrams

             WHERE    diagram_id = @CurrentPos;

            SET @index = @index + @chunk;

        END

        INSERT INTO @tblOut    (diagramname, diagram_id, diagram_text, diagram_ASCII)

             VALUES            (@name,         @diagram_id, @line,        REPLACE(@lineASC,CHAR(13),''));

        SELECT @CurrentPos = MIN(diagram_id)

          FROM dbo.sysdiagrams

         WHERE diagram_id > @CurrentPos;

    END

    RETURN;

END