모든 테이블 중 특정하여 자료 검색하기

정말 가~끔 모든 테이블의 데이터를 검색하고 싶을때가 있다.

예를들면.. NAME이라는 컬럼에 들어있는 '홍길동'이라는 데이터를 전부 찾고 싶을때?

 

USE [DATABASE_NAME]    --데이터베이스 이름

DECLARE @TABLE    NVARCHAR(50) = '',
        @COLUMN NVARCHAR(50) = '',
        @ROWC    INT    = 9999,
        @ANS    NVARCHAR(50) = '',
        @QUR    NVARCHAR(999) = ''

    --LIKE %컬럼이름%을 만족하는 컬럼을 가진 모든 테이블 조회
    SELECT ROW_NUMBER() OVER (ORDER BY B.NAME) [ROW_NUM], B.NAME [TABLENM], A.NAME [COLUMNNM],
           TYPE_NAME(A.system_type_id) [TYPE_NM], A.SYSTEM_TYPE_ID [TYPE_CD]
      INTO #TEMP_TABLE
      FROM SYS.COLUMNS A
      LEFT JOIN SYS.OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID
     WHERE A.NAME LIKE '%컬럼이름%'
       AND A.SYSTEM_TYPE_ID IN (167, 231)    --VARCHAR와 NVARCHAR 타입의 컬럼만 조회
     ORDER BY B.NAME
     
       SET @ROWC = @@ROWCOUNT

--검색된 자료를 가지고 모든 테이블 검색
WHILE @ROWC > 0
BEGIN
    SELECT @TABLE = TABLENM, @COLUMN = COLUMNNM
      FROM #TEMP_TABLE
     WHERE ROW_NUM = @ROWC

      --실행. 검색할 조건 ㅓㄹ정
      EXEC ('SELECT ' + @COLUMN + ', ''' + @TABLE + ''' FROM ' + @TABLE + ' WHERE ' + @COLUMN + ' = ''검색할 조건''');
    
    IF @@ROWCOUNT > 0
        SET @ANS = @TABLE    --해당하는 테이블이름을 저장

    SET @ROWC = @ROWC - 1
END

SELECT @ANS

DROP TABLE #TEMP_TABLE