count how many store procedure we have in a table

• christophe.bernard-680841

  Say Hey Kid

  Points: 700

  More actions

  December 18, 2008 at 3:58 am

  #129330

  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

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  December 18, 2008 at 4:08 am

  #914377

  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

• Christo Wolmarans

  Ten Centuries

  Points: 1170

  More actions

  December 18, 2008 at 4:42 am

  #914399

  Thanks Mark!

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

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  December 18, 2008 at 5:08 am

  #914409

  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]