Printing Strings Longer Than 8000 Characters

  • Jeff Moden wrote:

    Michael L John wrote:

    I'm with Phil.  Why are you doing this?

    I can't speak for Ron but sometimes it's necessary to see, for example, the full monty for dynamic SQL.

    Presumably you have your DBs in source control, Jeff? If so, you always have whatever version of the code you are interested in at your fingertips.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • That's true, Phil... but I don't keep the results of Dynamic SQL in source control.  I don't keep the code that gets stored in cache that I interrogate with my sp_WhatsRunning or sp_ShowWorst code.  There are a thousand reasons why someone might want or need to display way more than 8000 characters.  It's even good for looking at LOBs that you want to parse or whatever that are unfortunately stored in a table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    That's true, Phil... but I don't keep the results of Dynamic SQL in source control.  I don't keep the code that gets stored in cache that I interrogate with my sp_WhatsRunning or sp_ShowWorst code.  There are a thousand reasons why someone might want or need to display way more than 8000 characters.  It's even good for looking at LOBs that you want to parse or whatever that are unfortunately stored in a table.

    There has been a miscommunication, I think.

    I was not referring to the 8,000 character limit. I was questioning the generation of DDL for the objects in a database.

    • This reply was modified 3 years, 1 month ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Agreed.  I was only reading what you quoted and that was my comment about Dynamic SQL.  I totally agree DDL should be in source control.  Sometimes, though, source control and what's actually in a prod box are different.  It, of course, should never get that on the trunk until a branch is committed but I've seen people screw things up pretty badly on source control.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This is a stored procedure that I created a to print strings that exceed the 8K limit of the PRINT command.

    USE master;
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE OR ALTER PROCEDURE dbo.sp_LongPrint
    /* ===================================================================================================
    2017-01-05 Jason Long, Allows the printing of strings that are greater that the 4K limit of the PRINT commant.
    =================================================================================================== *//*
    DECLARE @x NVARCHAR(MAX) = (SELECT REPLICATE(CONVERT(NVARCHAR(MAX), 'abcdefghijklmnop' + char(13) + char(10)), 10000) );
    EXEC dbo.sp_LongPrint @x;
    */@_string NVARCHAR(MAX)
    AS
    BEGIN
    SET NOCOUNT ON;

    IF LEN(@_string) <= 4000
    BEGIN
    PRINT(@_string);
    END;
    ELSE
    BEGIN
    DECLARE @string_part NVARCHAR(4000);

    WHILE 1 = 1
    BEGIN
    SELECT
    @string_part = SUBSTRING(@_string, 1, ISNULL(NULLIF(se.string_end, 4000) - 1, 4000)),
    @_string = STUFF(@_string, 1, ISNULL(NULLIF(se.string_end, 4000) + 1, 4000), N'')
    FROM
    ( VALUES ( 4000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@_string, 1, 4000)))) ) se (string_end)

    IF @string_part = N''
    BEGIN
    BREAK;
    END;
    ELSE
    BEGIN
    PRINT(@string_part);
    END;
    END;
    END;
    END;
    GO

    EXEC sys.sp_MS_marksystemobject N'dbo.sp_LongPrint';
    GO
  • To expand on the reason I'm not using other DDL scripting methods is because I'm looping through hundreds of tables and running tests on converting data types and changing data in some columns. So one time through I might change all decimal(10,4) fields to  decimal(10,2) and next time through to an int. There will also be functions manipulating data in other fields so scripting and doing a find and replace would be more work in my opinion.

    I decided to select the strings into a table because this gave me the best solution to copying and pasting the DDLs I need.

    Jeff - your function is very handy and I'll have to keep that in my back pocket

    Jason - that does print out more than 8,000 characters but it breaks it up into different lines which was my initial problem

  • RonMexico wrote:

    To expand on the reason I'm not using other DDL scripting methods is because I'm looping through hundreds of tables and running tests on converting data types and changing data in some columns. So one time through I might change all decimal(10,4) fields to  decimal(10,2) and next time through to an int. There will also be functions manipulating data in other fields so scripting and doing a find and replace would be more work in my opinion.

    I decided to select the strings into a table because this gave me the best solution to copying and pasting the DDLs I need.

    Jeff - your function is very handy and I'll have to keep that in my back pocket

    Jason - that does print out more than 8,000 characters but it breaks it up into different lines which was my initial problem

     

    Ahhhhhh.  Fun stuff.

    As long as you are going to use the "Print" statement, it's never going to give you more than 8096 characters.  That's the limit for SSMS in an output window.

    Have you tried sending the results to a file???   That worked for me when I was faced with a similar situation

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • No luck with the results to file. Thanks for the help though

  • RonMexico wrote:

    No luck with the results to file. Thanks for the help though

    Perhaps BCP from a LOB column to a file?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply