메뉴 건너뛰기

kode2.com

MSSQL PROCEDURE 프로시저 내용 검색

2019.02.16 15:56

kode2 조회 수:563

여러 Database를 관리할때 특정 문구가 들어있는 Procedure를 찾고자 할때 사용할 수 있다.

 

 

 

USE master

 

 

 

DECLARE curj CURSOR 

 

FOR

 

SELECT name AS DBNAME

 

FROM master..sysdatabases

 

WHERE databasepropertyex(name, 'status')='ONLINE'

 

AND name NOT IN ('master','tempdb','model','msdb','pubs','Northwind')

 

 

 

 

 

OPEN curj

 

declare @strSQL nvarchar(4000)

 

declare @DBNAME varchar(30)

 

declare @ProcecureNm varchar(50)

 

declare @srchstr varchar(50)

 

DECLARE @params nvarchar(100)

 

DECLARE @retval bit

 

 

 

set @ProcecureNm=''

 

set @srchstr='sync'

 

 

 

FETCH NEXT FROM curj INTO @DBNAME

 

WHILE @@FETCH_STATUS = 0

 

BEGIN

 

 

 

 set @strSQL = 'SET @IsExists=0'

 

 set @strSQL = @strSQL + 'IF EXISTS(SELECT A.NAME FROM '+@DBNAME+'..SYSOBJECTS A, '+@DBNAME+'..SYSCOMMENTS B '

 

 set @strSQL = @strSQL + ' WHERE A.ID = B.ID '

 

 if @ProcecureNm<>'' 

 

 begin

 

  set @strSQL = @strSQL + ' AND A.NAME='''+@ProcecureNm+''''

 

 end

 

 if @srchstr<>'' 

 

 begin

 

  set @strSQL = @strSQL + ' AND B.TEXT LIKE ''%'+@srchstr+'%'''+ CHAR(10)

 

 end

 

 set @strSQL = @strSQL + ' )'+ CHAR(10)

 

 set @strSQL = @strSQL + ' SET @IsExists=1'+ CHAR(10)

 

 set @strSQL = @strSQL + 'ELSE'+ CHAR(10)

 

 set @strSQL = @strSQL + ' SET @IsExists=0'+ CHAR(10)

 

 SET @params = ' @IsExists AS bit OUTPUT' -- OUTPUT 키워드에 주의

 

 EXEC sp_executesql @strSQL, @params, @IsExists = @retval OUTPUT 

 

 

 

 IF @retval=0

 

  print @DBNAME + ' 없음'

 

 

 

FETCH NEXT FROM curj INTO @DBNAME

 

END

 

 

 

CLOSE curj

 

DEALLOCATE curj