January 10, 2019 at 10:36 am
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
January 10, 2019 at 10:44 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 14, 2019 at 12:21 am
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