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' |
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
