formatting lost when copying from one table to another (Solved)

  • Remarks: It was a problem with default settings in options in SSMS which prevented LF/CR not to be copied

    Hi

    I am trying to back up stored procedures. I copy them into the first table, and then copy only the updated ones into a table with fewer columns. Somehow information seems to get lost and I cannot explain how.

    First step:
    insert into dbo.x_tbl_info_USPs_Stored_Procs_complete
    select *
    from ASAP_be.information_schema.routines
    where routine_type = 'PROCEDURE'

    In this table the field [ROUTINE_DEFINITION] is fine, if I copy and paste back in to SSMS stored procedure is nicely formatted

    Second step:
    select
           x.[SPECIFIC_CATALOG],x.[ROUTINE_NAME]
          ,x.[ROUTINE_SCHEMA],x.[ROUTINE_DEFINITION]
             ,x.ROUTINE_CATALOG,x.ROUTINE_TYPE
          ,x.[SQL_DATA_ACCESS],x.[CREATED]
          ,x.[LAST_ALTERED]

    from x_tbl_info_USPs_Stored_Procs_complete as x
    left join
           x_tbl_info_USPs_Stored_Procs_short as s
    on
    x.ROUTINE_NAME=s.ROUTINE_NAME
    and
    x.LAST_ALTERED = s.LAST_ALTERED
    where
    s.ROUTINE_NAME is null

    if I now take the same field and copy it back so SSMS, it is all on one line.

    the field has the same definition nvarchar(4000) in both tables.
    Thanks for helping a newbie

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • Don't use that view; because:
    (1) it only returns 4000 chars, no matter how long the actual proc is.
    (2) INFORMATION_SCHEMA views run slowly and generate overhead that's not needed.

    To get the text of procedures (and functions and any other code stored in SQL), use view:
    sys.sql_modules.

    The sql code is then returned in an nvarchar(max) value, which can of course handle any length of code.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • thanks scott, I had the suspicion about the 4000 characters length.

    mistery solved about missing linebreaks/-feeds:
    in default linefeeds are not copied. so I had to go to tool-options, Query Results, SQL-Server, Results to Grid and check "Retain CR/LF on Copy/Save".

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

Viewing 3 posts - 1 through 2 (of 2 total)

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