SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기

SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

특정 문자열을 사용하는 값을 찾을 때 일반적으로 like를 사용한다. 예를들면 아래 스크립트과 같이 특정 컬럼을 지정하여 그 안에서 like 검색을 한다.

select * from Person.Address where city like '%test%'

 

한 컬럼에서 찾을 때에는 매번 손으로 타이핑하여 찾을 수도 있지만 테이블의 여러 컬럼을 확인확인 할 때 컬럼명을 계속 입력하는 것도 매우 불편한 일이다. 아래 스크립트를 사용하여 SP를 생성하여 사용하면 테이블의 모든 컬럼에서 문자열 검색을 사용할 수 있어 편리하다.

USE master GO   CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname AS   DECLARE @sqlCommand VARCHAR(8000) DECLARE @where VARCHAR(8000) DECLARE @columnName sysname DECLARE @cursor VARCHAR(8000)   BEGIN TRY SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' SET @where = ''   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''' + @schema + ''' AND TABLE_NAME = ''' + @table + ''' AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'   EXEC (@cursor)   OPEN col_cursor FETCH NEXT FROM col_cursor INTO @columnName   WHILE @@FETCH_STATUS = 0 BEGIN IF @where <> '' SET @where = @where + ' OR'   SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' FETCH NEXT FROM col_cursor INTO @columnName END   CLOSE col_cursor DEALLOCATE col_cursor   SET @sqlCommand = @sqlCommand + @where --PRINT @sqlCommand EXEC (@sqlCommand) END TRY BEGIN CATCH PRINT 'There was an error. Check to make sure object exists.' IF CURSOR_STATUS('variable', 'col_cursor') <> -3 BEGIN CLOSE col_cursor DEALLOCATE col_cursor END END CATCH

 

프로시저 생성이 완료되면 다음과 같은 방법으로 사용할 수 있다.

@stringToFind : 검색하려는 문자열 입력. '%test%', 'test%', %test'등 와일드 카드를 사용 가능

@Schema : 스키마 소유자

@table : 검색할 테이블명

EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

 

 

EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

 

 

EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'

 

 

 

 

2015-10-05 / 강성욱 / http://sqlmvp.kr