Searching stored procedure for table name but ignoring comments

  • Hi All,

    Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list those that reference a specific table (one way listed below ) but ideally I would like to exclude those table references that are listed in comments or hashed out and no longer part of the active body of the procedure ..

    Currently can't see a way to do this - has anyone got an idea on how to approach this or have actioned this themselves ?

    It may be this is just not possible searching through the sys tables ..but thought I would just ask

    Any feedback/tips much appreciated

    Thanks,

    Ralph

    SELECT DISTINCT SO.NAME

    FROM SYSOBJECTS SO (NOLOCK)

    WHERE SO.TYPE = 'P'

    AND SO.NAME LIKE @StringToSearch

    ORDER BY SO.Name

  • Unless you get incredibly sophisticated with your search algorithms, possibly using CLR or something, to determine if the string you're looking at is contained within a comment, of either type, line comments or block comments, no, there's no way to do this with a straight-forward T-SQL statement.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Wouldn't a search of dependencies tell you?

    {edit} Bad suggestion on my part. :blush: Like Steve says below, it's NOT 100% guaranteed. It's always worked for me in the past but that's only because I (apparently) got lucky and followed "the rules". I knew that dynamic SQL wouldn't register but I didn't know the "fault" induced by deferred naming, etc.

    See sys.sql_dependencies in Books Online for more info on why a dependency may or may not exist.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If the dependency tree in SQL Server is up to date it works, but it isn't guaranteed. I'd agree with Grant, there isn't an easy way to do this. What you could do is just search, and if you find tables in stored procedure comments, go through and clean up the comments to change the table name in there to something that won't match.

  • Here's what Steve means about "if they're up to date". This comes from Books Online under "sys.sql_dependencies"...

    [font="Arial Black"]Remarks[/font]

    Dependencies are established during CREATE only if the referenced (independent) entity exists at the time that the referencing (dependent) object is created. Due to deferred name resolution, the referenced entity need not exist at the time of creation. In this case, a dependency row is not created. Moreover, entities referenced through dynamic SQL do not establish dependencies.

    If the referenced (independent) entity is dropped using DROP, the dependency row is deleted automatically. To re-establish the dependency row, you will need to re-create both, using CREATE, in the correct dependency order.

    Both schema-bound and non-schema-bound dependencies are tracked for objects. Dependencies on types, XML schema collections, and partition functions are only tracked for schema-bound dependencies. CHECK constraints, defaults, and computed column references are implicitly schema-bound.

    Also note that further reading reveals that anything contained in dynamic SQL is NOT included, ever.

    So... with all that in mind, I'll have to withdraw my suggestion because it was a bad one. :blush:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi

    I did doubt there was a way to do this using T-SQL but really appreciate the feedback from everyone - I'll look at Steve's suggestion of performing the search then go through and clean up the comments to change the table name in there to something that doesn't match.

    Many thanks all

    Ralph

  • I have this snippets saved which might help; it's using a pair of loops table to strip out any comments , so you can then search just the remaining proc definition;

    declare @definition varchar(max),

    @objectname varchar(255),

    @vbCrLf CHAR(2)

    SET @vbCrLf = CHAR(13) + CHAR(10)

    SET @objectname = 'sp_getDDL'

    select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)

    --'objective: strip out comments.

    --first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.

    --second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.

    --===== Replace all '/*' and '*/' pairs with nothing

    WHILE CHARINDEX('/*',@definition) > 0

    SELECT @definition = STUFF(@definition,

    CHARINDEX('/*',@definition),

    CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term

    '')

    --===== Replace all single line comments

    WHILE CHARINDEX('--',@definition) > 0

    AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)

    SELECT @definition = STUFF(@definition,

    CHARINDEX('--',@definition),

    CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,

    '')

    print @definition --you can now search this without false positives from comments.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi

    This really helps a lot - can build up list of all objects referencing the table then run through this.:-)

    Much appreciated

    Many Thanks

    Ralph

  • You could also use this free utility application to search SQL code while optionally ignoring comments, and/or to script out selected SQL routines in correct dependency order...

    http://www.DBGizmo.net

    Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

  • Lowell (11/28/2010)


    I have this snippets saved which might help; it's using a pair of loops table to strip out any comments , so you can then search just the remaining proc definition;

    declare @definition varchar(max),

    @objectname varchar(255),

    @vbCrLf CHAR(2)

    SET @vbCrLf = CHAR(13) + CHAR(10)

    SET @objectname = 'sp_getDDL'

    select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)

    --'objective: strip out comments.

    --first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.

    --second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.

    --===== Replace all '/*' and '*/' pairs with nothing

    WHILE CHARINDEX('/*',@definition) > 0

    SELECT @definition = STUFF(@definition,

    CHARINDEX('/*',@definition),

    CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term

    '')

    --===== Replace all single line comments

    WHILE CHARINDEX('--',@definition) > 0

    AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)

    SELECT @definition = STUFF(@definition,

    CHARINDEX('--',@definition),

    CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,

    '')

    print @definition --you can now search this without false positives from comments.

    That rocks! I used that...

  • I have used this. This will help us to ignore the commented lines as well as the line prior to the CREATE statetments. Look like conventional but helpful

    --Exec dbo.search 'test'

    Create Procedure [dbo].[search] (@searchstr varchar(100))

    As

    Declare @cntr int

    Declare @cnt int

    Declare @proc_name varchar(100)

    Declare @proc_type varchar(100)

    Declare @seq int

    Declare @out_str varchar(255)

    Create Table #proc_names (sl_no int identity(1,1), proc_name varchar(100),type varchar(100))

    Insert into #proc_names

    Select name,Case xtype When 'P' Then 'Stored Procedure'

    When 'V' Then 'View'

    When 'TR' Then 'Trigger'

    When 'FN' Then 'Function' End

    from dbo.sysobjects where xtype in ('P','V','TR','FN') and uid = 1

    Create Table #proc_script (seq_no int identity(1,1), proc_str text)

    Create Table #Rtn_proc_str (sl_no int identity(1,1), object_name varchar(100), object_type varchar(100), errortext varchar(255))

    Set @cntr = 1

    Select @cnt = count(1) from #proc_names

    While @cntr <> @cnt

    Begin

    Select @proc_name = proc_name, @proc_type = type from #proc_names where sl_no = @cntr

    BEGIN TRY

    Insert into #proc_script

    Exec dbo.sp_helptext @proc_name

    END TRY

    BEGIN CATCH

    Set @out_str = ERROR_MESSAGE()

    Insert into #Rtn_proc_str

    Values (@proc_name, @proc_type, @out_str)

    END CATCH

    Select @seq = seq_no from #proc_script where proc_str like 'Create %'

    Delete from #proc_script where seq_no < @seq

    Delete from #proc_script where proc_str like '--%'

    If exists (Select 1 from #proc_script where proc_str like '%'+@searchstr+'%')

    Begin

    Insert into #Rtn_proc_str

    Values (@proc_name, @proc_type,'')

    End

    Truncate Table #proc_script

    Set @cntr = @cntr + 1

    End

    Select object_name, object_type, errortext from #Rtn_proc_str order by object_type, object_name

  • The script is great!!!

    Do you happen to have one that does the exact opposite? I'm doing Peer Review of a DB project and need to find all SP without a comments section.

    Thanks

  • Hi, Lowell!

    How do you actually call your code snippet that ignores text inside comments?

    --Brian

  • brian wow it's been a while since i saw this thread.

    Today, I actually do the same with regular expressions and a CLR; if that's an option, let me know and i'll pso tthat too.

    here's a little more advanced snippet , all via TSQL, which creates a temp table of all definitions, and then strips out the comments, based on my earlier post.

    in theory, the finished temp table has just the object definitions, so if you search THAT, you get just the items that truly reference the string you search for:

    IF (SELECT OBJECT_ID('Tempdb.dbo.#tmp')) IS NOT NULL

    DROP TABLE #tmp

    SELECT

    Schema_Name(objz.schema_id) As SchemaName,

    objz.name As ObjectName,

    objz.type As TypeCode,

    objz.type_desc As ObjectType,

    modz.definition

    into #tmp

    FROM sys.objects objz

    INNER JOIN sys.sql_modules modz

    on objz.object_id = modz.object_id

    --################################################################################################

    --Pre Step

    --generic cleanup:

    --some definitions may end in only vbLf / CHAR(10), and not my convention of vbCrLf/CHAR(13) + CHAR(10)

    --this cleanup is REQUIRED because we need some sort common of End-Of-Line indicator for single line comments.

    UPDATE #tmp

    SET [definition] = REPLACE([definition],CHAR(10),CHAR(13) + CHAR(10))

    UPDATE #tmp

    SET [definition] = REPLACE([definition],CHAR(13) + CHAR(10) + CHAR(10),CHAR(13) + CHAR(10))

    --################################################################################################

    --'objective: strip out comments.

    --first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.

    --===== Replace all '/*' and '*/' pairs with nothing

    WHILE EXISTS(SELECT 1 FROM #tmp WHERE CHARINDEX('/*',[definition]) > 0 )

    BEGIN

    UPDATE #tmp

    SET [definition] = STUFF([definition],

    CHARINDEX('/*',[definition]),

    CHARINDEX('*/',[definition]) - CHARINDEX('/*',[definition]) + 2, --2 is the length of the search term

    '')

    WHERE CHARINDEX('/*',[definition]) > 0

    IF @@ROWCOUNT = 0

    BREAK;

    END --WHILE

    --################################################################################################

    --second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.

    --===== Replace all single line comments

    WHILE EXISTS(SELECT 1 FROM #tmp

    WHERE CHARINDEX('--',[definition]) > 0

    AND CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) > 0 )

    BEGIN

    UPDATE #tmp

    SET [definition] = STUFF([definition],

    CHARINDEX('--',[definition]),

    CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) - CHARINDEX('--',[definition]) + 2,

    '')

    WHERE CHARINDEX('--',[definition]) > 0

    AND CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) > 0

    IF @@ROWCOUNT = 0

    BREAK;

    END --WHILE

    SELECT * FROM #tmp

    --WHERE definition LIKE 'MyObjectName%'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Cool, I'll try playing with that.

    What I'm trying to do is create a Stored Procedure that will take a string of text as a parameter, and then search all the database objects, in all schemas, within a given database, and return a table showing all the objects the string appears, or optionally, to do the same but ignore any object where the search string only appears in the comments. And I think I need to create it in the master database to make it available in all the other databases on my server.

Viewing 15 posts - 1 through 15 (of 16 total)

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