count how many store procedure we have in a table

  • Hi everybody,

    i work with sql server 2005 and i'm a beginner !

    i have a db who have some table

    and on certain table there are many many store procedure !

    i would like to count how many store procedure there is on a table !

    is there a store procedure for to do this !?

    or i must to create one ?

    Thanks in advance

    Christophe

  • See if this helps

    select distinct object_name(id)

    from sys.sysdepends

    where object_name(depid)='mytable'

    and objectproperty (id,'IsProcedure') = 1

    Also suggest looking at sys.sp_refreshsqlmodule to update the dependencies first (I'm not sure this is foolproof though)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks Mark!

    This really helped me a bunch and I think it's going to help me solve some problems I have been having 😀

  • One comment I'd like to make.

    In SQL 2005 when using sys.sysdependencies it does not always show all depend objects. Especially in the case where you create the procedure first while the table doesn't exist no dependency is registered. This happens a lot when you generate a script from a database and use it to create a new database.

    In SQL 2008 there is a new catalog view sys.sql_expression_dependencies which solves this problem.

    Here's an example:

    CREATE PROCEDURE DemoProc

    AS

    SELECT c1,c2

    FROM t1

    GO

    CREATE TABLE t1 (

    c1 INT,

    c2 VARCHAR(2)

    )

    go

    -- No dependency is found

    select distinct object_name(id)

    from sys.sysdepends

    where object_name(depid)='t1'

    and objectproperty (id,'IsProcedure') = 1

    -- SQL 2008 only

    select distinct object_name(referencing_id)

    from sys.sql_expression_dependencies

    where object_name(referenced_id)='t1'

    and objectproperty (referencing_id,'IsProcedure') = 1

    [font="Verdana"]Markus Bohse[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

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