[MS-SQL] 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
사용 예제
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명'
