Results 1 to 12 of 12

Thread: Sp Execution

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Sp Execution

    Is there a way to tell if a stored proc has ever been executed? I've taken over another system where the developers backed up there procs by creating them with a different name. Of course there's no pattern to the naming convention either.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    What was it that Shakespeare said? "1st kill all the developers..."

    [just kidding, don't call Homeland Security, I don't want to get arrested for making terroristic threats! ]

    Try taking a look at the syscacheobjects table in the master database. It is far from perfect, but if you take a snapshot of it several days (maybe weeks) in succession, then you might get a better picture of what's running (and not running) on your server.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many sprocs are there?

    And what about Security?

    What are the grants like? Is it EXECUTE only on the Sprocs?

    If not, what about dynamic sql? Or are you just trying to do a cleanup?

    If it's just for the sake of cleaning, I wouldn't touch a thing.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    1) Luckily no dynamic SQL. About 400 sprocs across 7 dbs. The real number is probably around 150 if we can remove the crap procs.
    2) Permissions, well the app logs in as dbo so no objects have any permissions granted to them. Thankfully the app only executes procs and doesn't query tables or views directly. We'll fix this issue when we get a chance.
    3) Finally, the reason I'm doing this is for cleanup but primarily performance tuning. We're having performance problems and I'd like to analyze the indexes on the major tables. It sux tryng to determine which indexes need to exist when your looking at a where clause in a proc that isn't used anymore.

    Another problem I've noticed is the developers went hog wild with sp_rename. So the names of the procs don't match the DDL generated by EM. This is causing some problems with my documentation and reverse engineering tools. At least I assume it's an sp_rename issue.
    Last edited by peterlemonjello; 12-13-05 at 14:12.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could probably add an insert statement to all of them. Create a log table of ProcedureName, RunDate. Then in the background, the procedure will insert it's name and current date into this table, for later reporting.

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    Yeah, that's my last resort. I thought about pushing the work back on the developers. Have them log every proc their code calls for a few weeks and go from there.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many of these do you get?

    SELECT o.id, COUNT(*)
    FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id
    WHERE o.type = 'P'
    GROUP BY o.id
    HAVING COUNT(*) > 1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    What does that do???

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Tells me how many procedures have over 4000 bytes of code.

    I'm thinking there is a way to automate the logging by doing an ALTER.

    It would be a fun exercise. In syscomments there is a row for each procedure that have 4,000 bytes.

    It would also be a good tool for me in the future....as long as it doesn't screw up the sprocs
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    For example

    Code:
    SELECT o.id, o.name
    , REPLACE(c.text
    	,'as'+CHAR(13)
    	,'as INSERT INTO LOG SELECT ' + '''' + o.Name + '''' + ',' 
    		+ CONVERT(varchar(5),@@SPID) + ',' 
    		+ ''''+ SYSTEM_USER + '''')
    FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id
    WHERE o.type = 'P'
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh, oh! Can I geek too?
    Code:
    SELECT Sum(w), n
       FROM (SELECT Cast(Object_Name(c.id) AS VARCHAR(255)) AS n
    ,     DataLength(c.text) AS w
          FROM dbo.syscomments AS c) AS z
       GROUP BY n
       ORDER BY 1 DESC, n
    -PatP

  12. #12
    Join Date
    Sep 2003
    Posts
    364
    Brett, great idea, unfortunately it only returned around 120 procs.

Posting Permissions

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