Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831

    Unanswered: get dml and references

    Not getting the reference dml I was looking for...maybe someone can spot the problem for me.

    Code:
    -- get dml and references
    select distinct 'Object'=object_name(l.id),
    l.dml,
    type = substring(v.name, 5, 16),
    l.reference
    from (
    select distinct c.id,
    	'Object'=object_name(c.id),
    	'DML'=case when patindex('%insert into%',text) !=0 then 'insert'
    				 when patindex('%update%',text) !=0 then 'update'
    				 when patindex('%delete%',text) !=0 and  patindex('%deleted%',text) = 0 then 'delete'
    				 when patindex('%select%',text) !=0 then 'select'
    	end,
    	'Reference'=case when patindex('%insert%',text) != 0 then 
    						case when substring(text,patindex('%insert%',text)+12,1) = '(' then substring(text,patindex('%(%',text),patindex('%(%',text)-patindex('%insert%',text)) 
    							else substring(text,patindex('%insert%',text)+12,25)
    							end
    					 when patindex('%update%',text) !=0 then substring(text,patindex('%update%',text)+7,25)
    					 when patindex('%delete%',text) !=0 then 
    						case when patindex('% from %',text) !=0 then substring(text,patindex('% from %',text)+6,25) 
    							else substring(text,patindex('%delete%',text),25)
    						end
    					 	
    					 when patindex('%select%',text) !=0 and patindex('% from %',text) !=0 then substring(text,patindex('% from %',text)+5,25)
    	end
    	from syscomments c
    	where substring(object_name(c.id),1,3) not in ('sys','dt_')
    	and (patindex('%insert%',text) >= 1
    	or patindex('%update%',text) >= 1
    	or (patindex('%delete%',text) >=1 and patindex('%deleted%',text) =0)
    	or (patindex('%select%',text) >= 1 and patindex('%from %',text) >=1))
    ) l
    inner join sysobjects o on l.id = o.id
    inner join master.dbo.spt_values v on v.type = 'O9T' and o.type = substring(v.name,1,2) collate database_default
    where l.dml != 'NULL' 
    order by 1

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    Hi

    It took a while to understand what you are trying to achieve.
    You are trying to get a list of sql objects(function, view, sp, trigger, etc) what their activities are. The activities include INSERT, UPDATE, DELETE, SELECT. Then refer to the target objects.

    One of the defects that I discovered was
    patindex('% from %',text)
    This was used in conjunction with SELECT.clause.
    But some cases FROM clause may be put one line below without leading blank.
    In this case, reference cannot be retrieved.
    Therefore I suggest to change it to
    patindex('%from %',text)

    As this is text scan in pattern search, you cannot have 100 % accurate result.
    Good luck.

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Hi Terry,

    Yep, I want to extract the reference dml (from) clause in statement. The objective is to identify all the select, inserts, updates, deletes and associated dml then list the reference dml. This information is really for documentation and also to help me better understand what relationships exist between procedures.

    I'll tweak it some more.

    Cheers,

    Jon

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Minor, but if this is 2005+ you'll do a bit better with sys.sql_modules rather than syscomments.

    Also, would this not be more accurate?
    Code:
    or text LIKE '%delete[^d]%'

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by pootle flump View Post
    Minor, but if this is 2005+ you'll do a bit better with sys.sql_modules rather than syscomments.

    Also, would this not be more accurate?
    Code:
    or text LIKE '%delete[^d]%'
    Interesting enough, I ran the following two code snippets and the results differed:

    -- returned 22 objects (did not return deleted from table ref in trigger)
    select distinct o.name
    from sysobjects o
    inner join syscomments c on o.id = c.id
    where c.text like '%delete[^d]%'

    -- returned 23 objects (returned deleted from table ref in trigger)
    select distinct o.name
    from sysobjects o
    inner join syscomments c on o.id = c.id
    where patindex('%delete%',text) != 0

    So the patindex is more accurate?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    LIKE will be more efficient than PATINDEX but really I was correcting a logical flaw.

    Code:
    SELECT  *
    FROM    
            (
                SELECT  text   = 'delete and deleted'
            ) AS tester
    WHERE   text LIKE '%delete[^d]%'
    
    SELECT  *
    FROM    
            (
                SELECT  text   = 'delete and deleted'
            ) AS tester
    WHERE   patindex('%delete%',text) >=1 and patindex('%deleted%',text) =0

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, won't you need to iterate over all the code in the module to pick up all references?

    Not being sure of your exact requirements, I would be tempted to use sp_sysdepends either instead of this or in conjunction.

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Ok! Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •