[MSSQL] db내 모든 테이블에서 자료를 검색

<사용방법은..>

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

위처럼 사용합니다. % 와 _로 여러글자 매칭과 한글자 매칭가능.

 

 

 

/*************************************************************************/
/*         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                                                                   */
/*************************************************************************/
CREATE PROCEDURE spSearchOnAlldb @phrase varchar(8000), @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(8000)
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 varchar(7500) 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(varchar(7500),'+@col+') '
                 +'from '+@tbl+' (nolock) '
                 +'where convert(varchar(8000),'+@col+') like '+char(39)+@phrase+char(39)
  end
  if @OutFullRecords=1
  begin
    set @sql='if exists (select * from '+@tbl+' (nolock) '
                       +'where convert(varchar(8000),'+@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(varchar(8000),'+@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(varchar(255),ColumnValue) ColumnValue from #tbl_res order by 1,2,3')
end
  
drop table #tbl_res

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

다른방법

-- 간단하게 테스트 삼아 만들어 봤습니다.

 

-- 테이블, 컬럼 추출

 SELECT B.name AS TABLE_NAME, A.name AS COLUMN_NAME INTO #TMP FROM syscolumns A
 INNER JOIN  sysobjects B ON A.id = B.id AND B.type='u'
 INNER JOIN systypes C ON A.xtype=C.xtype
 AND C.name in ('text', 'ntext', 'char', 'nchar', 'varchar', 'nvarchar')

 

--- 특정테이블

DECLARE @tablename sysname
DECLARE @Where NVarchar(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @SEARCH NVARCHAR(200)

SET @tablename = '테이블명'
SET @SEARCH = '가나다라'
SET @Where = ''

SELECT @Where = @Where + COLUMN_NAME + ' LIKE ''%' + @Search + '%'' OR '   FROM #TMP 
WHERE TABLE_NAME =  @tablename
SET @Where = LEFT(@Where, Len(@Where) - 3)

SET @SQL = 'SELECT * FROM ' + @tablename + ' WHERE  '+@Where

EXEC SP_EXECUTESQL @SQL

------

 

 

-- 전체 테이블
DECLARE @tablename sysname
DECLARE @Where NVarchar(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @SEARCH NVARCHAR(200)

SET @SEARCH = '가나다라'

DECLARE CUR_TABLE CURSOR
 READ_ONLY FOR 
 SELECT TABLE_NAME FROM #TMP 
 GROUP BY TABLE_NAME

Open CUR_TABLE

FETCH NEXT FROM CUR_TABLE into @tablename
WHILE (@@fetch_status <> -1)
Begin
 
 SET @WHERE = ''
 SET @PARAMS = ''
 SET @SQL = ''
  
 SELECT @Where = @Where + COLUMN_NAME + ' LIKE ''%' + @Search + '%'' OR '   FROM #TMP 
 WHERE TABLE_NAME =  @tablename
 
 SET @Where = LEFT(@Where, Len(@Where) - 3)

 
 IF Len(@Where ) > 0
 Begin
 
  
 
  SET @SQL = 'SELECT * FROM ' + @tablename + ' WHERE ' + @Where
  
  
  
  EXEC SP_EXECUTESQL @SQL      
  
 End  
  
 
 FETCH NEXT FROM CUR_TABLE into @tablename

End

CLOSE CUR_TABLE

DEALLOCATE CUR_TABLE