전체 SP/테이블 대상 검색 방법

아래 쿼리를 활용하여 조회하고자 하는 항목을 검색 가능하다.

전체 대상으로 조회 시, 범위가 방대하므로 조건절로 대상을 축소하여 처리하면 유용하다.

--00.전체 SP에서의 텍스트 조회

SELECT DISTINCT 'EXEC sp_helptext [' + SCHEMA_NAME(SCHEMA_ID) + '.' + OBJECT_NAME(A.id) + ']'

FROM sys.syscomments A INNER JOIN sys.objects B ON A.id = B.object_id

WHERE TEXT LIKE '%조회하고자 하는 텍스트%'

 

--01.전체 테이블 내 데이터 조회

DECLARE @SearchStr NVARCHAR(100)

SET @SearchStr = 'dbkim'

 

CREATE TABLE #Results (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','int','decimal')

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

 

DROP TABLE #Results