MSSQL Meta 검색

모든테이블에서 데이터로 검색

DECLARE @SearchValue NVARCHAR(100) = N'검색값';
DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchSQL NVARCHAR(MAX);

DECLARE TableCursor CURSOR FOR
SELECT t.name, c.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext'); -- 문자형 컬럼만 검색

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SearchSQL = 
        'IF EXISTS (SELECT 1 FROM [' + @TableName + '] WHERE [' + @ColumnName + '] LIKE N''%' + @SearchValue + '%'') 
         PRINT ''테이블 [' + @TableName + '] 컬럼 [' + @ColumnName + '] 에 존재함''';
    EXEC (@SearchSQL);

    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

 

✅ 테이블 코멘트(extended property) 검색 쿼리

 

✅ 테이블 코멘트(extended property) 검색 쿼리
SELECT  
    s.name AS SchemaName,
    t.name AS TableName,
    ep.name AS PropertyName,
    CAST(ep.value AS NVARCHAR(500)) AS Comment
FROM sys.extended_properties ep
JOIN sys.tables t ON ep.major_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ep.name = 'MS_Description'          -- 표준 코멘트 프로퍼티명
  AND CAST(ep.value AS NVARCHAR(500)) LIKE N'%검색어%'  -- 검색어 입력
ORDER BY s.name, t.name;

 

 

예를 들어 특정 컬럼 이름이 포함된 테이블을 찾고 싶다면 👇

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%컬럼명%'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

 

또는 좀 더 상세한 정보(테이블 ID 등)까지 보고 싶다면 sys 뷰를 사용할 수 있어요:

 

SELECT 
    t.name AS TableName,
    c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%컬럼명%'
ORDER BY t.name;

💡 LIKE '%컬럼명%' 대신 = '정확한컬럼명' 으로 하면 완전 일치 검색이 돼요.
💡 뷰(View)까지 포함해서 찾고 싶다면 sys.objects를 조인해도 됩니다.

예시 (테이블 + 뷰 포함):

 

SELECT 
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    c.name AS ColumnName
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
WHERE c.name LIKE '%컬럼명%'
ORDER BY o.type_desc, o.name;