which proc use table a?

• cruzings

  SSC Journeyman

  Points: 94

  More actions

  December 21, 2006 at 10:35 pm

  #114983

  does anyone have a script to search the db for procs, functions that use certain tables?

• MohammedU

  SSCoach

  Points: 19644

  More actions

  December 22, 2006 at 12:34 am

  #679658

  select

  distinct object_name(id) from syscomments where text like '%Table Name%'

  MohammedU
  Microsoft SQL Server MVP

• Lowell

  SSC Guru

  Points: 323547

  More actions

  December 22, 2006 at 9:48 am

  #679769

  ive used this in the past; it's a cursor, and resource intensive; it takes a couple of minutes to run on my 100meg database with 100's of procs and 1400+tables:

  sample results:

  ProcName	MightReferenceObject
  PR_ACTDELFAVORITES	GMACT
  PR_ACTDELFAVORITES	GMACTFAVORITES
  PR_COMPLETEDELETE_ACT	GMACTFAVORITES
  PR_COMPLETEDELETE_ACT	GMHOPWA6
  PR_COMPLETEDELETE_ACT	GMHOPWA7

  --search all procedures and find out if the name of any of the objects appear in their text:

  SET NOCOUNT ON

  declare

   @isql varchar(2000),

   @objname varchar(64)

   --All User Tables, Views, Procedures, Table function or Scalar Function.

          Create Table #MightReference(

                          ProcName varchar(60),

                          MightReferenceObject varchar(60)

                       )

   declare c1 cursor for select name from sysobjects where xtype in ('U','V','P','TF','FN')

   open c1

   fetch next from c1 into @objname

   While @@fetch_status <> -1

    begin

                  --desired logic: if an object name exists in the syscomments of a procedure,

                  --it MIGHT mean the procedure references the object

                  --insert the two into a table for further evaulation.

                   Insert into #MightReference(ProcName,MightReferenceObject)

       select object_name(id),@objname from syscomments

                              where id in(select id from sysobjects where xtype='P' and name NOT like 'dt_%') 

                              and text like '%' + @objname + '%' 

    fetch next from c1 into @objname

    end

   close c1

   deallocate c1

          --avoid self referencing procs. "CREATE PROC MYPROC" statement returns itself!

          Select * from #MightReference

          where ProcName <> MightReferenceObject

          order by ProcName 

          Select * from #MightReference

          where ProcName <> MightReferenceObject

          order by MightReferenceObject

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!