여러 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