MSSQL] 프로시저, 뷰 문자열 검색

매번 프로시저, 뷰를 찾는 쿼리를 작성하는 번거로움을 없애기 위해, SearchProc 프로시저를 만들어 놓고 사용한다. 

 

EXEC SearchProc '찾는문자열', 'dbname'

 

 CREATE  PROCEDURE [dbo].[SearchProc]  
 @text nvarchar(250),   
 @dbname nvarchar(64) = null    
 AS   
  
  
 SET NOCOUNT ON  
     
 DECLARE @TABLE TABLE  
 (  
  NID INT IDENTITY(1,1),  
  NAME VARCHAR(100)  
 )  
  
DECLARE @결과 TABLE  
 (  
  NID INT IDENTITY(1,1),  
  DBNAME VARCHAR(100),  
  NAME VARCHAR(100),  
  XTYPE VARCHAR(100),  
  TYPE VARCHAR(100),  
  TEXT TEXT   
 )  
  
DECLARE   
 @I INT,  
 @MAX INT,  
 @NAME VARCHAR(100),  
 @SQL Nvarchar(2000)  
  
INSERT @TABLE (NAME)  
 SELECT NAME  
 FROM Master..SYSDATABASES  
 WHERE NAME NOT IN ('master', 'tempdb')  
  
   
  
IF(@dbname <> '')  
 BEGIN  
  DELETE @TABLE WHERE NAME <> @dbname  
 END  
  
  
 SELECT @I = 1, @MAX = MAX(NID) FROM @TABLE  
 WHILE(@I <= @MAX)  
 BEGIN  
  SELECT @NAME = [NAME] FROM @TABLE WHERE NID = @I  
    
  
 SET @SQL = 'SELECT ''' + @NAME + ''' AS DBNAME, A.[NAME], A.[XTYPE], A.[TYPE], B.[TEXT]'  
       + 'FROM ' + @NAME + '..SYSOBJECTS A '  
       + 'JOIN ' + @NAME + '..SYSCOMMENTS B ON A.ID = B.ID '  
       + 'WHERE A.[ID] IN ( '  
       +  'SELECT DISTINCT A.[ID] '  
       +  'FROM ' + @NAME + '..SYSOBJECTS A '  
       +  'JOIN ' + @NAME + '..SYSCOMMENTS B ON A.ID = B.ID '  
       +  'WHERE A.XTYPE IN (''FN'', ''IF'', ''P'', ''RF'', ''TF'', ''TR'', ''U'', ''V'', ''X'') '  
       +  'AND B.TEXT LIKE ''%'' + @SearchWord + ''%'' '  
       + ') '  
       + 'ORDER BY A.[NAME] '  
  
  EXECUTE SP_EXECUTESQL @SQL, N'@SearchWord nvarchar(128)', @SearchWord = @text  
  SET @I = @I + 1  
  
   
  
END