SQL Server에서 특정 컬럼을 포함하는 모든 테이블의 데이터 검색하기

SQL Server에서 특정 컬럼을 포함하는 모든 테이블의 데이터 검색하기

때로는 SQL Server 내에서 특정 컬럼명을 가진 모든 테이블에서 데이터를 검색해야 할 필요가 있습니다. 이러한 작업을 수행하기 위해 동적 SQL을 사용하여 쿼리를 생성하고 실행할 수 있습니다.

동적 SQL 스크립트 예제

다음은 주어진

테이블 및 컬럼명에 대하여 동적인 쿼리를 생성하고 그 쿼리를 실행하는 SQL 스크립트의 예입니다.

DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @SearchCode NVARCHAR(10) = '홍길동'; --조회할 데이터
DECLARE @ColumnName NVARCHAR(128) = '이름'; --조회할 컬럼명
DECLARE @TablePattern NVARCHAR(128) = '%고객%'; --조회할 테이블명

SELECT @SQL += N'SELECT ''' + t.TABLE_NAME + ''' AS TableName, * FROM ' + QUOTENAME(t.TABLE_NAME) +
              N' WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + @SearchCode + '%''; '
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE  1 = 1
AND t.TABLE_NAME LIKE @TablePattern
AND c.COLUMN_NAME = @ColumnName;

-- SQL 확인을 위한 출력 (옵션)
PRINT @SQL;

-- SQL 실행
EXEC sp_executesql @SQL;

스크립트의 활용

변수 @ColumnName 와 @TablePattern 를 사용하여 원하는 컬럼명과 테이블명 패턴을 설정할 수 있습니다. 이를 통해 스크립트를 다양한 경우에 적용하고 재사용할 수 있습니다.

주의사항

동적 SQL은 매우 강력한 도구이지만, 사용자 입력을 직접 쿼리에 연결할 경우 보안 문제가 발생할 수 있습니다. 위의 스크립트에서는 LIKE 절에서 사용자 입력(@SearchCode)을 직접 연결하고 있으므로, 스크립트를 그대로 사용하기 전에 필요한 적절한 검증 및 에스케이핑을 수행해야 합니다. 특히, SQL 인젝션 공격을 방지하기 위해 사용자 입력을 적절하게 검증하고 처리하는 것이 중요합니다.