[MSSQL] DB에서 특정 문자열을 찾고 싶을 때

가끔 일을 하다보면 특정문자가 포함된 테이블이 뭔지 찾고싶을때가 있다.

혹은 단편적인 단서로 실마리를 찾아야 할 때 라던가…

그래서 두가지 방법을 소개해본다.

 

1) 전체 DB에서 특정 문자열을 찾고싶을 때

USE [kitty]
GO
/****** Object: StoredProcedure [dbo].[spSearchOnAlldb] Script Date: 2018-08-16 오후 5:12:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*************************************************************************/
/* Procedure of search of a phrase on all database */
/* Is developed by Oufimtsev Gleb, MCSE */
/* */
/* gvu@newmail.ru, http://www.gvu.newmail.ru */ 
/* +7 (095) 178-40-92, Moscow, Russia */
/*************************************************************************/
ALTER PROCEDURE [dbo].[spSearchOnAlldb] @phrase varchar(4000), @OutFullRecords bit=0 AS

/*
To apply so: 
exec spSearchOnAlldb 'Sugar%'
exec spSearchOnAlldb '%soft%'
exec spSearchOnAlldb '_5234_57%', 1
exec spSearchOnAlldb M_cro_oft
*/

declare @sql varchar(4000)
declare @tbl varchar(128) 
declare @col varchar(128)
declare @id_present bit

declare @is_char_phrase bit
declare @min_len int
declare @loop_idx int
declare @loop_chr char(1)


set nocount on

if IsNull(@phrase,'')=''
begin
raiserror('Phrase is absent',16,-1)
return
end
select @loop_idx=1, @is_char_phrase=0, @min_len=0

while @loop_idx<=LEN(@phrase)
begin
set @loop_chr=SUBSTRING(@phrase,@loop_idx,1)
if @loop_chr not in ('%','_') set @min_len=@min_len+1
if @is_char_phrase=0 and @loop_chr not in ('%','_','0','1','2','3','4','5','6','7','8','9','.') 
set @is_char_phrase=1
set @loop_idx=@loop_idx+1
end

create table #tbl_res 
(TableName varchar(128) not NULL,
ColumnName varchar(128) not NULL,
Id int NULL,
ColumnValue nvarchar(max) not NULL)

declare CRR cursor local fast_forward for
select t.name, c.name, 1 
from sysobjects t, syscolumns c 
where t.type='U'
and c.id=t.id
and c.status&0x80=0 -- Not IDENTITY
and exists (select * from syscolumns c2 where t.id=c2.id and c2.status&0x80=0x80 and c2.xtype in (48,52,56))
and ( (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and c.length>=@min_len) -- char only
or (@is_char_phrase=0 and c.xtype not in (34,165,173,189,61,58,36))) -- char and numeric
union select t.name, c.name, 0 
from sysobjects t, syscolumns c 
where t.type='U'
and c.id=t.id
and not exists (select * from syscolumns c2 where t.id=c2.id and c2.status&0x80=0x80 and c2.xtype in (48,52,56))
and ( (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and c.length>=@min_len) -- char only
or (@is_char_phrase=0 and c.xtype not in (34,165,173,189,61,58,36))) -- char and numeric
order by 1,2
open CRR
fetch CRR into @tbl, @col, @id_present
while @@FETCH_STATUS=0
begin
if @OutFullRecords=0
begin
set @sql='insert into #tbl_res (TableName,ColumnName,Id,ColumnValue) '
+'select '+char(39)+@tbl+char(39)+', '
+char(39)+@col+char(39)+', '
if @id_present=1 set @sql=@sql+'IDENTITYCOL, '
else set @sql=@sql+'NULL, ' 
set @sql=@sql+'convert(nvarchar(3500),'+@col+') '
+'from '+@tbl+' (nolock) '
+'where convert(nvarchar(4000),'+@col+') like '+char(39)+@phrase+char(39)
end
if @OutFullRecords=1
begin
set @sql='if exists (select * from '+@tbl+' (nolock) '
+'where convert(nvarchar(4000),'+@col+') like '+char(39)+@phrase+char(39)+') '
+'select '+char(39)+@tbl+char(39)+' TableName, '+char(39)+@col+char(39)+' ColumnName, * '
+'from '+@tbl+' (nolock) where convert(nvarchar(4000),'+@col+') like '+char(39)+@phrase+char(39)
end
exec(@sql)
fetch CRR into @tbl, @col, @id_present
end
close CRR
deallocate CRR


if @OutFullRecords=0
begin
-- For the clients supporting new types:
--exec('select * from #tbl_res order by 1,2,3')


-- For the clients who are not supporting new types:
exec('select TableName, ColumnName, Id, convert(nvarchar(255),ColumnValue) ColumnValue from #tbl_res order by 1,2,3')
end

drop table #tbl_res

예제는 프로시저 SQL에도 나와있듯이

exec  spSearchOnAlldb '%찾고싶은 문자열%'

 

2) 또 다른 방법.

USE [Kitty]
GO
/****** Object:  StoredProcedure [dbo].[U_SearchOnAlldb]    Script Date: 2020-01-23 오전 10:53:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*************************************************************************/
/* 전체 DB에서 특정 문자열 검색                                          */
/* Kim Kitty                                                     */
/*************************************************************************/
--exec [dbo].[U_SearchOnAlldb] 'Kitty'

-----------------------------------------------------

CREATE PROC [dbo].[U_SearchOnAlldb]
(
    @SearchStr nvarchar(100)
)
AS

BEGIN

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

 

 

 

3) 특정 테이블에서 특정문자열을 찾고 싶을 때

USE [kitty]
GO
/****** Object: StoredProcedure [dbo].[sp_FindStringInTable] Script Date: 2018-08-16 오후 5:11:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[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

예제는

EXEC sp_FindStringInTable '%찾고싶은 문자열%', 'dbo', 'Table명'

 

Post Author: 김 키티

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다