SAP:打印存储源代码

打印SAP存储过程、函数、视图源代码

 

-- SELECT count(1) FROM [SCSP]
USE [SBO-COMMON]
GO
/*
SELECT TOP 900 spname, dobefore, ServerType FROM [SCSP]
where 1=1 -- and spname like 'tmfn%' --'TmSp_UpgradeElectronicInvoiceMX' -- '_TmSp_ChangeColumnToNotNull'
 and spName+cast(ServerType as nvarchar(20))  in  (select s2.spName+ cast( max(s2.ServerType) as nvarchar(20)) 
  from scsp  s2 where 1=1 -- and  s2.spname like '_TmSp_ChangeColumnToNotNull'
   group by s2.spname )
order by spName
*/
GO
-- SQL-打印存储过程源码(未加密)
DECLARE C_SP CURSOR  FOR
 SELECT SCSP.spName AS STORE_PROCEDURE,
        N'未加密'  AS [IS_ENCRYPT],
        scsp.SpString AS [DEFINITION],
		DATALENGTH(SCSP.SPString) AS STRING_LONG
  FROM [SCSP]
 where 1=1  
  and spname like '_TmSp_CreateViewB1_AllDocumentsView%' ESCAPE '\' -- '_TmSp_ChangeColumnToNotNull'
 and spName+cast(ServerType as nvarchar(20))  in  (select s2.spName+ cast( max(s2.ServerType) as nvarchar(20)) 
  from scsp  s2 where 1=1 -- and  s2.spname like '_TmSp_ChangeColumnToNotNull'
   group by s2.spname )
 order by scsp.SpName ;

DECLARE @STORE_PROCEDURE NVARCHAR(128),
 @IS_ENCRYPT NVARCHAR(20), 
 @DEFINITION NVARCHAR(MAX),
 @STRING_LONG INT; -- 第4000字段一段; 
declare @times int, --  数
 @i int ,
 @j int ,
 @start int;  
BEGIN
-- SQL SERVER PRINT打印函数 每次最多只支持4000BYTE. 故打印前先对内容进行分段。
  OPEN C_SP ;
  FETCH NEXT FROM C_SP INTO  @STORE_PROCEDURE, @IS_ENCRYPT, @DEFINITION,@string_long
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @times = CEILING(@STRING_LONG/4000.0);
    set @i= 1;
    IF @IS_ENCRYPT =N'未加密'
    BEGIN
     WHILE @i <=@times
     begin
       set @start = 1+ (@i-1)*4000.0;
       print Substring(@definition,@start,4000.0)
       set @i= @i+1;
     end;
     PRINT ''
     print 'GO'+ CHAR(10)
     END;
      
     /*
    begin
      if @STRING_LONG>4000 and @STRING_LONG<8000
      begin
        print substring(@definition,1,4000)
        print subString(@definition,4001,8000)
      end 
      else 
        print @definition;       
      print 'GO'
    end;
    */
   FETCH NEXT FROM C_SP INTO  @STORE_PROCEDURE, @IS_ENCRYPT, @DEFINITION,@string_long
  END;
  CLOSE C_SP;
  DEALLOCATE C_SP;
END;