像USER_SOURCE oracle中的LINE这样的sql server中的行号 [英] Line number in sql server like LINE in USER_SOURCE oracle

问题描述

如果要更改一些存储过程代码，我正在尝试找出影响.

I am trying to findout impact if i change some stored procedure code.

在oracle中，我以前喜欢

In oracle i used to do like

select NAME,TEXT,LINE from USER_SOURCE where upper(TEXT) like '%SEARCH_STRING%'

在我尝试使用的sql server中

in sql server i tried as

 SELECT DISTINCT so.name,sc.TEXT 
    FROM syscomments sc 
    INNER JOIN sysobjects so ON sc.id=so.id 
    WHERE sc.TEXT LIKE '%SEARCH_STRING%'

有什么方法可以从sql server中的存储过程中获取文本的行号?

Is there any way to get line number of text from stored procedure in sql server?

推荐答案

我实际上经常做这件事，所以我仔细研究了一下，发现了我多年前为此目的编写的函数:

I actually used to do this a lot, so I dug around and found this function I wrote many years ago for just this purpose:

CREATE function [dbo].[fnSplit3]( 
                @parameter varchar(Max)                -- the string to split
                , @Seperator Varchar(64)        -- the string to use as a seperator
        ) 
        RETURNS @Items TABLE(
                ID INT                                                -- the element number
                , item VARCHAR(8000)                -- the split-out string element
                , OffSet int                                -- the original offest
                --( not entirley accurate if LEN(@Seperator) > 1 because of the Replace() )
        ) 
AS
BEGIN 
/*
"Monster" Split in SQL Server 2005; From Jeff Moden, 2008/05/22

BYoung, 2008/06/18: Modified to be a Table-Valued Function
                    And to handle CL/LF or LF-only line breaks
  (Note: making it inline made it slower, not faster)

Test: (scripts all triggers in your database)

        Select Lines.Item
         From sys.sql_modules M
          Join sys.objects O on O.object_id = M.object_id
          cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines
         Where O.Type = 'TR' 
         Order by O.create_date, Lines.ID
*/
Declare @Sep char(1)
Set @Sep = char(10)        --our seperator character (convenient, doesn't affect performance)
--NOTE: we make the @Sep character LF so that we will automatically
-- parse out rogue LF-only line breaks.

--===== Add start and end seprators to the Parameter so we can handle
        -- all the elements the same way
        --  Also change the seperator expressions to our seperator
        -- character to keep all offsets = 1
SET @Parameter = @Sep+ Replace(@Parameter,@Seperator,@Sep) +@Sep
-- This reduces run-time about 10%

;WITH cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
        -- of the parameter is
 SELECT TOP (LEN(@Parameter))
        ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
  FROM Master.sys.sysColumns t1
   CROSS JOIN Master.sys.sysColumns t2
)
INSERT into @Items
        SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
                SUBSTRING(@Parameter, N+1, CHARINDEX(@Sep, @Parameter, N+1)-N-1) AS Value
                , N+1
         FROM cteTally
         WHERE N < LEN(@Parameter)
          AND SUBSTRING(@Parameter, N, 1) = @Sep --Notice how we find the seperator

        Return 
END

现在有更快的版本，但是这个版本仍然比您在此处看到的约90％的拆分功能要快.如果您在注释中签出测试示例，则几乎完全符合您的要求.

There are faster version out there now, but this one is still faster than about 90% of the split functions you'll see here. If you check out the test example in the comments, its almost exactly what you asked for.

        Select O.name, Lines.Item, Lines.ID As LineNo
         From sys.sql_modules M
          Join sys.objects O on O.object_id = M.object_id
          cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines
         Where O.Type = 'P' 
           And Lines.Item LIKE '%SEARCH_STRING%'
         Order by O.name, Lines.ID

这篇关于像USER_SOURCE oracle中的LINE这样的sql server中的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！