Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Edit 1600+ stored procedures at once

    I'm looking for a way to track usage of SPs. I haven't found any TSQL tool that is already tracking that information. Long ago another DBA added an exec to the end of some SPs to log the usage to a table.

    If there is a function already tracking this I'd like to know.

    If not, I want to tack an exec line to the end of all the SPs but I don't want to do it by adding it 1600 times manually. Is there any trick in the scripting process I can do to make this easier?

    JB

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Code:
    Select 
    	Replace(sc.Text +
    	Case 
    		When sc.colid = s0.maxColid Then N'Exec your_proc' + NChar(10)
    		Else NChar(10)
    	End , N'Create Proc', 'Alter Proc')  + NChar(10) + N'Go'
    From syscomments sc
    	Join sysobjects so On sc.id = so.id
    	Join (	Select so.id, Max(colid) As 'maxColId'
    			From syscomments sc
    				Join sysobjects so On sc.id = so.id
    			Where so.type = 'P'
    			Group By so.id
    		) s0 On so.id = s0.id
    Where so.type = 'P'
    Order By so.name, sc.colid
    Last edited by MaxA; 02-23-05 at 01:30.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  3. #3
    Join Date
    Oct 2003
    Posts
    7

    Thanks

    This is awesome! I did have to take it half at a time due to the limitation of row size, but it appears it did just what I wanted. Thank you very much. This will help for many other projects.

    John

Posting Permissions

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