메뉴 건너뛰기

Dev tips

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************  
*작성자 :   
*작성일 : 
*내  용 : DataBase 안에 있는 procedure에 텍스트 내용을 검색한다

*실  행 : exec [dbo].[find_text_in_sp]'검색하려는 텍스트',  '대상 DataBase'
*결  과 :  
******************************************************/  
create PROCEDURE [dbo].[find_text_in_sp]  
  @text varchar(250),  
  @dbname varchar(64) = null  
AS BEGIN  
SET NOCOUNT ON;  
  
if @dbname is null  
  begin  
   --enumerate all databases.  
 DECLARE #db CURSOR FOR Select Name from master..sysdatabases  
  declare @c_dbname varchar(64)  
  
  OPEN #db FETCH #db INTO @c_dbname  
  while @@FETCH_STATUS <> -1 --and @MyCount < 500  
   begin  
     execute find_text_in_sp @text, @c_dbname  
     FETCH #db INTO @c_dbname  
   end    
  CLOSE #db DEALLOCATE #db  
 end --if @dbname is null  
else  
 begin --@dbname is not null  
  declare @sql varchar(250)  
  --create the find like command  
  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '  
  select @sql = @sql + ' from '+@dbname+'.sys.sql_modules m '  
  select @sql = @sql + ' inner join '+@dbname+'..sysobjects o on m.object_id=o.id'  
  select @sql = @sql + ' where [definition] like ''%'+@text+'%'''  
  execute (@sql)  
 end --@dbname is not null  
END  

GO