Post reply

Find text in body of the database objects

  • October 29, 2013 at 2:14 am

    #279318

    How to find any text in the body of the procedure, function, view etc.?

    www.fcbarcelona.extremazing.com - FC Barcelona - extreme and amazing team

  • October 29, 2013 at 2:19 am

    #1662233

    Create procedure on your database:

    CREATE PROC __FindText

    @query varchar(100)

    AS

    SELECT DISTINCT name, type

    FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id

    WHERE text LIKE '%' + @query + '%'

    ORDER BY name

    Then you can use this command

    exec __findtext 'your text'

    or

    __findtext 'your text'

    http://www.learn-with-video-tutorials.com/how-to-find-a-text-inside-sql-server-code - this video demonstrate how to use sys.syscomments sys.sysobjects to find text inside code in SQL Server objects.

    www.learn-with-video-tutorials.com - video tutorials

  • October 31, 2013 at 2:53 pm

    #1663271

    If you don't need it proceduralized, you can also use sp_helptext to quickly get that information.

    ex.

    exec MyDB.dbo.sp_helptext myView

    exec MyDB.dbo.sp_helptext myProc

    Executive Junior Cowboy Developer, Esq.[/url]

  • October 31, 2013 at 3:09 pm

    #1663274

    sysobjects and syscomments are deprecated (have been for 8 years), should not be used any longer.

    Use sys.objects and sys.sql_modules.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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