CODEWITHSUNDEEP
sql-serveroracle-databasestored-procedures
Icarus
Icarus

Reputation: 455

Line number in sql server like LINE in USER_SOURCE oracle

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

In oracle i used to do like

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

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%'

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

Upvotes: 2

Views: 3415

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56745

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

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

Upvotes: 1

Related Questions