▷ Mssql tables with specific values (MSSQL 특정값을 가지고 있는 테이블 찾기)

[ Finding tables with specific values ]

/*
@NAME : FindTablesWithString
@DESC : 특정 문자열을 포함하는
특정 테이블의 특정 필드명 찾는 프로시저

@USAGE: EXEC FindTablesWithString '문자열' (찾는 문자열)
예) 1. exec FindTablesWithString 'test'
   2. exec FindTablesWithString '%test%'
*/


SET ANSI_NULLS ON
GO

CREATE PROC FindTablesWithString
@STR2FIND NVARCHAR(128)
AS
BEGIN

-- (1개 행 적용됨) 와 같은 메세지를 보이지 않게 하기 위함
set nocount on

-- 테이블명
declare @tbl_name nvarchar(128)
-- 필드명
declare @col_name nvarchar(128)

declare @strsql nvarchar(512)
--declare @str2find nvarchar(128)

declare @params nvarchar(128)
declare @cnt int
declare @col_val nvarchar(128)

-- 찾고자하는 문자열
--set @str2find = '%' + @str2find + '%'

--정보를 저장할 임시 테이블
CREATE TABLE #FindTBL (
Tblname varchar(128),
Colname varchar(128),
value varchar(128)
)

declare tbl_cursor cursor  for select name from sysobjects where xtype = 'U'
open tbl_cursor
fetch next from tbl_cursor into @tbl_name

while (@@fetch_status = 0)
begin
declare col_cursor cursor for select name from syscolumns a
     where a.id=object_id(@tbl_name)
     and xtype in (175, 239, 167, 231) -- 175:char, 239:nchar, 167:varchar, 231:nvarchar
     --and isnull(collation,'') <> 'Korean_Wansung_CS_AS_KS_WS'
open col_cursor
fetch next from col_cursor  into @col_name

while (@@fetch_status = 0)
begin
set @strsql = 'select @pcnt = count(*), @pcol_val = [' + @col_name + '] from ' + @tbl_name + ' where [' + @col_name + '] like ' + '''' +  @str2find + '''' + ' group by [' + @col_name + ']'
--print @strsql
--exec(@strsql)

set @params='@pcnt int OUTPUT,  @pcol_val nvarchar(80) OUTPUT'
exec sp_executesql @strsql, @params, @pcnt = @cnt OUTPUT, @pcol_val = @col_val OUTPUT -- nvarchar 타입이어야만 함 (OUTPUT을 이용)

if (@cnt > 0 and @@error = 0)
begin
--select @tbl_name as 'a', @col_name as 'b', @col_val 'c'
insert into #FindTBL values(@tbl_name, @col_name, @col_val)
end

set @cnt = 0

fetch next from col_cursor into @col_name
end

close col_cursor
deallocate col_cursor

fetch next from tbl_cursor into @tbl_name
end

close tbl_cursor
deallocate  tbl_cursor

-- 찾은 테이블 테이블/필드명으로 정렬 보기
select Tblname as '테이블명'
, Colname '필드명'
, Value '문자열'
from #FindTBL
order by tblname, colname

-- 임시 테이블 삭제
DROP TABLE #FindTBL

SET NOCOUNT OFF

END;

SET ANSI_NULLS ON
GO