Search for string occurrence

  • Can this be done?

    Does anyone know of a tool or command I could use that can find all occurrences of a particular string?

    I want to look in all of my procs and triggers.

    For example, I have an event_code column on some tables. I reference that column in my procs and triggers. In my scripts, I may have something like @event_code = 'DDENT'. I want to search through all procs and triggers for 'DDENT'.

    I have all of my procs & triggers in .sql files and I could search using windows explorer for the DDENT string but I don't trust the results.

    Thank you

  • You can script all database objects into one file. Then simply search through the file for all objects. Works with the MMC plugin for 7/2000.

  • "You can script all database objects into one file." How do I do this?

    What is MMC plugin for 7/2000 and how/where can I get it?

  • select

    distinct object_name(id) from syscomments where text like '%DDENT%'

    This will return the object names that contain the string "DDENT".

    Hope that helps,

  • Karl -

    That worked great - thanks!

  • The MMC is the enterprise consule. Basically right click on the database and script to file. It should let you select all objects.

    The 2nd option appears to work better but I was not familiar with it.

    good luck

    daralick

  • SysComments is a system table that holds all the scripts of all the objects of the database (sps, triggers, views...).  So this technic is really fast an effective to find out stuff like that.

  • The [text] column in syscomments is nvarchar(4000). If the length of the text for the object is greater that 4000, it will be split into two or more rows. Be advised that when searching syscomments with LIKE, there is a slight possibility that the string you are searching for could be split and cross over into the next row. In that case, LIKE will not find a match. Granted, it's a relatively remote possibility, but it could happen.

  • Ya I had to code for that once... Since I had a application I did 2 searchs... The first search was only considering objects with one entry, then the second search downloaded the code in the application, the app concatenated all parts into a single script and then the search was ran.  I'm sure a similar process could be done in a dts but I don't have the time to code it now.

  • Good point.

    You can do this entirely in T-SQL by concatenating the [text] field into a seperate table that has a field with a text datatype (thus allowing more than 8000 characters).  You can then safely perform a like comparison.

    It's a bit awkward though and given how remote the chances of this occurring are, unless it's an essential part of an app you're building, you might as well just script the procs and do a Find/Replace search.  Or accept the risk.

  • I can see that you've got the problem solved but another suggestion would be the text editor TextPad (http://www.textpad.com/).  It has a very good "Find In Files" facility and is a reasonable text editor.

  • That's a nice idea, but it doesn't solve the problem that first have to script all the procs to a file before search... that's why I preffer a on server processing.  That way you always get the real result in no time at all (once you've written the simple code once).

  • Here's some quick and dirty code that will just print out the object name. Sorry, but I used a cursor. Since I'm assuming this is for an ad-hoc administration task, I'm don't mind using the cursor. Also, I left some debugging code in (it is commented out).

    To run the stored procedure after it is created, don't include the '%' around the search string, as they are appended in the SPROC:

    -- EXEC uspFindStringInObjectSourceCode 'string to search for'

    Here is the stored procedure code:

    IF EXISTS(SELECT name

       FROM   sysobjects

       WHERE  name = N'uspFindStringInObjectSourceCode'

       AND   type = 'P')

        DROP PROCEDURE uspFindStringInObjectSourceCode

    GO

    CREATE PROCEDURE uspFindStringInObjectSourceCode

    (

      @search varchar(1000)

    )

    AS

    -- For each object (stored procedure, function, or trigger),

    -- concatenate the text from all sysmessages rows into a

    -- single text column in a temp table.

    SET NOCOUNT ON

    CREATE TABLE #text

    (

      id int

    , newtext ntext

    )

    DECLARE @objId int

          , @colId smallint

          , @ntext nvarchar(4000)

    DECLARE @curObj int

          , @tPtr varbinary(16)

    DECLARE cObjText CURSOR READ_ONLY FOR

      SELECT c.[id]

           , c.colid

           , c.[text]

        FROM syscomments c

        JOIN sysobjects o

          ON c.id = o.id

       WHERE o.xtype IN ('P', 'FN', 'TF', 'IF', 'TR', 'V')

       ORDER BY c.[id], c.colid

    OPEN cObjText

    -- Get first row from cursor, set current obj to -1 to force start of new object

    FETCH cObjText INTO @objId, @colId, @ntext

    SET @curObj = -1

    --DECLARE @newObjName varchar(100)

    --      , @prevColId smallint

    WHILE @@FETCH_STATUS = 0

    BEGIN

      IF @curOBj <> @objId  --start a new object

      BEGIN

        SET @curOBj = @objId

    --    IF @newObjName IS NOT NULL

    --      PRINT Convert(varchar(8), @prevColId) + ' row(s) processed'

    --    SELECT @newObjName = name

    --      FROM sysobjects

    --     WHERE id = @objId

    --    PRINT 'New object: ' + @newObjName

        INSERT #text (id, newtext) VALUES (@objId, @ntext)

        SELECT @tPtr = TextPtr(newtext) FROM #text WHERE [id] = @objId

      END

      ELSE --continue with current object

       

      BEGIN

    --    PRINT 'Appending row ' + Convert(varchar(8), @colId)

        UPDATETEXT #text.newtext @tPtr NULL NULL @ntext

      END

    --SET @prevColId = @ColId

      -- get next row from cursor

      FETCH cObjText INTO @objId, @colId, @ntext

    END --WHILE

    CLOSE cObjText

    DEALLOCATE cObjText

    --SELECT id

    --     , Datalength(newtext) AS numBytes

    --     , Datalength(newtext) / 2 AS numChars

    --     , Substring(newtext,1,200) AS BeginningOfText

    --  FROM #text

    SELECT [id]

         , Datalength(newtext) AS numBytes

         , Datalength(newtext) / 2 AS numChars

         , (SELECT Min(name) FROM sysobjects WHERE id = t.id) AS ObjName

      FROM #text t

     WHERE newtext LIKE '%' + @search + '%'

     ORDER BY 4

    DROP TABLE #text

    SET NOCOUNT OFF

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply