為什麼我的拆分字元串函式不適用於大型 OBJECT_DEFINITION？

我的一個數據庫中有一個儲存過程

稱為程序$$ repl $$.$$ usp_upd_repl_out_application $$，這不是一個非常聰明的儲存過程，但它有超過 389 行。

由於我需要更改此儲存過程，因此我喜歡在進行任何更改之前將其備份到表中，我知道原始碼控制應用程序，這應該是要走的路，但我很高興以我的方式做事，它適用於我。

這可能是另一種重新發明輪子的情況，但我仍然想知道為什麼我的拆分字元串函式失敗了。

因此，在嘗試備份此儲存過程時，此儲存過程是 my_database 中的 object_id = 146815585。

這完美地工作：

DECLARE 
 @olddelim nvarchar(32) = char(13) + Char(10),
 @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)
SELECT * 
 FROM STRING_SPLIT(REPLACE(OBJECT_DEFINITION(146815585)
 , @olddelim
 , @newdelim)
 , @newdelim);

這也很好用，雖然這不是我想要的方式：

if object_id('tempdb..#radhe') is not null
  drop table #radhe

create table #radhe(i int identity(1,1) not null primary key clustered, ln nvarchar(max))
insert into #radhe (ln)
exec sp_helptext '[repl].[usp_upd_repl_out_application]'

我從這裡得到了這個拆分功能：

ALTER FUNCTION dbo.fn_TVF_Split(@arr AS NVARCHAR(max), @sep AS NCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING,ENCRYPTION
AS
RETURN
WITH 
  L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
 ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
 ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
 ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
 ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
 ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
 ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  
SELECT
(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element
FROM Nums
WHERE 
   n <= LEN(@arr) + 1
   AND SUBSTRING(@sep + @arr, n, 1) = @sep
   AND N<=1000
GO



DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10), 
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?) 
SELECT m.*  
--INTO TableBackups.dbo._MMiorelli_20180305_repluspupdreploutapplication_140331_DB_ID_32 
from  dbo.fn_TVF_Split( OBJECT_DEFINITION ( 146815585),  @olddelim) m  

它只返回 398 行中的 18 行

而下面的這個，只返回 398 個中的 60 個。

--=============================================
-- this is the SplitString function 
-- for use when we're below sql 2016
--=============================================
alter FUNCTION dbo.SplitString

   (
       @List NVARCHAR(MAX),
       @Delim NVARCHAR(255)
   )
   RETURNS TABLE
   WITH ENCRYPTION
   AS
       RETURN ( SELECT [Value] FROM 
         ( 
           SELECT 
             [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
             CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
           FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
             FROM sys.all_objects) AS x
             WHERE Number <= LEN(@List)
             AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
         ) AS y
       );

這就是我呼叫這個函式的方式：

DECLARE  
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10), 
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?) 

SELECT m.*  
from  master.dbo.splitstring( OBJECT_DEFINITION ( 146815585),  @olddelim) m  

返回 60 行。

題：

有沒有辦法可以修改我的函式master.dbo.splitstring，使其返回整個 398 行程序？

我認為您sys.all_objects沒有足夠的行來完成這項工作。看起來您的拆分常式正在讀取每個單獨的字元並將其連接起來。儲存過程中的字元數比 sys.all_objects 中的行數多。

我創建並填充了一個dbo.numbers包含 100,000 行的表，並修改了您的程式碼以使用它而不是sys.all_objects. 我還包括了我正在使用的 row_number，以便能夠查看“數字”是如何被消耗的。

如果您執行輸出到文本，您可以真正看到發生了什麼。

--drop table numbers
SELECT TOP 100000
       IDENTITY(INT,1,1) AS Number
  INTO dbo.numbers
  FROM Master.dbo.SysColumns sc1,
       Master.dbo.SysColumns sc2

--=============================================
-- this is the SplitString function 
-- for use when we're below sql 2016
--=============================================
alter FUNCTION dbo.SplitString

   (
       @List NVARCHAR(MAX),
       @Delim NVARCHAR(255)
   )
   RETURNS TABLE
   WITH ENCRYPTION
   AS
       RETURN ( SELECT * FROM 
         ( 
           SELECT 
           convert(varchar(10),number) as num,
             [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
             CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
           FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY number)
             FROM dbo.numbers) AS x
             WHERE Number <= LEN(@List)
             AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
         ) AS y
       );

DECLARE  
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10), 
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?) 

SELECT m.*  
from  dbo.splitstring( OBJECT_DEFINITION ( 727113781),  @olddelim) m  

引用自：https://dba.stackexchange.com/questions/199407