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

• christoph.kauflin

  SSC-Addicted

  Points: 415

  More actions

  January 10, 2019 at 10:36 am

  #381175

  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

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  January 10, 2019 at 10:44 am

  #2018591

  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".

• christoph.kauflin

  SSC-Addicted

  Points: 415

  More actions

  January 14, 2019 at 12:21 am

  #2018803

  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