Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Posts
    24

    Unanswered: how to check stored procedure status

    Hi,

    Could anyone please let me how to check status(valid/invalid) for all stored procedure in my DB.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Can you elaborate on your question ?
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Nov 2003
    Posts
    24
    Originally posted by Enigma
    Can you elaborate on your question ?
    I want to check stored procedures status.... how do I kbow that which procedure is a valid and which is invalid (due to any reason).

    I beleive this is pretty much elaborated!!!!!!!!!!

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What do you mean by an invalid stored procedure ????
    Forgive my not understanding .. but what is an invalid stored procedure .

    A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. So if the transact statements are valid .. which they should be to form a sproc .. how can the sproc be invalid ???
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, its not.

  6. #6
    Join Date
    Nov 2003
    Posts
    24
    Originally posted by blindman
    No, its not.
    If at a time you created a procedure that is referencing a couple of tables and later if you droped any of the refereneced table then what wiould happen with that procedure????????????????????????????????????????? ?????????????????????????????????????????????????? ????????

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    One way that i can think of is run a sp_depends for all the stored procedures .. insert into a temp table ... and then check whether the tables exist in sysobjects.

    That brings us to another question , what if you did not drop a table but changed a column that was being used in sproc .... how would i find out that ???
    Get yourself a copy of the The Holy Book

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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by nmajeed
    beleive this is pretty much elaborated!!!!!!!!!!
    Well I'm glad you think so

    referencing a couple of tables and later if you droped any of the refereneced table then what wiould happen with that procedure
    Lots of 'tude lately...why didn't you just say so?

    And Enigma..the horse is out of the barn...this is soemthing that has to happen BEFORE you drop the tables....

    Code:
    CREATE TABLE myTable99 (Col1 int)
    GO
    CREATE PROC mySproc99 AS SELECT * FROM myTable99
    GO
    
    sp_Depends mySproc99
    GO
    sp_Depends myTable99
    GO
    DROP TABLE myTable99
    GO
    sp_Depends mySproc99
    GO
    DROP PROC mySproc99
    GO
    [s]You might want to look at sp_recompile[/s]

    Nope that just marks it for recompile....
    Last edited by Brett Kaiser; 12-18-03 at 14:32.
    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.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    yup you are right Brett ... my bad .. the reference to object id will have disappeared in Sysobjects ...
    Get yourself a copy of the The Holy Book

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

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Plus sysdepends won't know about any dynamic references.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    then sp_recompile is the choice !!! good show brett ...
    Get yourself a copy of the The Holy Book

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

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, jolly good show old boy. Right-o! Bit of a sticky-wicket that one was.

  13. #13
    Join Date
    Jun 2012
    Posts
    4

    An alternative approach (doesn't require compiling objects)

    Another possible solution would be to write a TSQL script that identifies & loops through all user-defined stored procs, functions, and views; and in each iteration, perform the following:

    1. Find any/all db object references (via the sp_depends system stored procedure).

    2. Check for the existence of referenced db object(s) (via the object_id system function).

    3. Print the name of the “bad” stored proc/function/view.

  14. #14
    Join Date
    Jun 2012
    Posts
    4

    in lieu of using sp_depends...

    you can select from the sys.dm_sql_referenced_entities system view

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As was already pointed out in the thread (uhm....eight years ago...), neither of those methods will detect relationships in dynamic sql code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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