Tuesday, January 24, 2012

How to find table which used in many procedures in DataBase using Procedure

This post discuss in details at DataHaunting

We Many times want to find the table which used in various procedure 
Here I have Created simple procedure to find in which Procedure it is used

CREATE PROCEDURE [dbo].[prc_Search]    
@StringToSearch varchar(100)     
   SET @StringToSearch = '%' +@StringToSearch + '%'    
   SELECT Distinct SO.Name    
   FROM sysobjects SO (NOLOCK)    
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID    
   AND SO.Type = 'P'    
   AND SC.Text LIKE @stringtosearch    
   ORDER BY SO.Name  

create above procedure in your Database

Now when you want find your table is used in which procedure simply Execute
   prc_Search 'tbl_AdminUsers'
as given below slide you can see the number of procedure in which your procedure is used

No comments:

Post a Comment