MSSQL 전체 DB 및 테이블 조회 방법

최근에 MSSQL 을 유지관리하지 않던 업체에서 DB 점검 요청이 있어서 작업 진행을 했습니다.

 

[요청사항]

- 모든 테이블에서 특정 데이터의 형태를 추출

- 엑셀 형태의 정리 요청

 

 

이에 아래와 같이 전체 하나의 인스턴스 안에 있는 모든 사용자데이터베이스의 모든 테이블의 TOP3 를 조회하여 추출을 진행하였습니다.

 

MSSQL 전체 DB 및 테이블 조회 방법

 

1. 전체 DB 조회

exec sp_helpdb;

 

[결과]

전체 DB 조회

 

[체크 필요 데이터]

- Database 이름

- Database 크기(mdf+ldf)

- 소유자

- 생성일자

 

2. 특정 DB 의 전체 테이블 조회

USE [특정_DB_이름]; -- 조회할 데이터베이스 선택
GO

SELECT TABLE_SCHEMA AS SchemaName, 
       TABLE_NAME AS TableName, 
       TABLE_TYPE AS TableType
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'; -- BASE TABLE은 실제 테이블만 조회

 

[결과]

특정 DB 의 전체 테이블 조회

 

전체 스키마와 테이블 이름 확인이 가능합니다.

 

간단한 스키마와 테이블의 차이입니다.

 

한줄로 표현하자면 스키마가 테이블을 포함합니다.

구분	스키마(Schema)	테이블(Table)
역할	객체들을 그룹화하여 관리	데이터를 저장하는 실제 객체
위치	데이터베이스 내에서 테이블, 뷰 등을 포함	스키마 내부에서 생성
용도	보안, 네임스페이스 관리	데이터를 행(Row)과 열(Column)로 저장
예	dbo, sales, hr	employees, orders, products

 

3. 각 테이블의 TOP3 데이터 조회

 

USE [A]; -- 'A' 데이터베이스로 변경
GO

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tableName NVARCHAR(MAX);

-- 테이블 이름 리스트 생성
DECLARE table_cursor CURSOR FOR
SELECT 
    TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_TYPE = 'BASE TABLE'; -- 기본 테이블만 선택

-- 커서 열기
OPEN table_cursor;

-- 각 테이블에서 TOP 3 데이터 조회하는 SQL 생성
FETCH NEXT FROM table_cursor INTO @tableName;S

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @sql + N'SELECT TOP 3 * FROM ' + @tableName + N';' + CHAR(13) + CHAR(10);
    FETCH NEXT FROM table_cursor INTO @tableName;
END

-- 커서 닫기 및 해제
CLOSE table_cursor;
DEALLOCATE table_cursor;

-- 동적 SQL 실행
EXEC sp_executesql @sql;

 

[결과]

특정 DB 의 모든 테이블 TOP3 조회

 

이렇게 소량의 DB 는 하나씩 데이터를 뽑아낼 수도 있습니다.

하지만 저는 꽤 많은 DB가 있어서 작업이 필요했습니다.

 

4. 모든 DB 의 테이블 TOP3 조회 

각 DB 테이블 별로 csv 로 추출 --> ssms 에서는 모든 테이블을 csv 로 한번에 처리가 불가능.

이에 Powershell 을 통해 작업을 진행했습니다.

# SQL Server 연결 정보
$serverInstance = "localhost"  # SQL Server 인스턴스 이름 (예: localhost 또는 SERVERNAME\INSTANCENAME)
$outputFolder = "C:\SQLExports"           # CSV 파일 저장 경로

# 저장 폴더 생성
if (!(Test-Path $outputFolder)) { New-Item -ItemType Directory -Path $outputFolder }

# 데이터베이스 목록 가져오기
$databases = Invoke-Sqlcmd -ServerInstance $serverInstance -Query "SELECT name FROM sys.databases WHERE state = 0 AND name NOT IN ('master', 'tempdb', 'model', 'msdb')"

# 데이터베이스 순회
foreach ($db in $databases) {
    $dbName = $db.name
    Write-Host "Processing database: $dbName"

    # 테이블 목록 가져오기
    $tables = Invoke-Sqlcmd -ServerInstance $serverInstance -Database $dbName -Query "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"

    foreach ($table in $tables) {
        $schemaName = $table.TABLE_SCHEMA
        $tableName = $table.TABLE_NAME

        # TOP 3 데이터 추출 (맨 앞 열에 DatabaseName 추가)
        $query = "SELECT TOP 3 '$dbName' AS DatabaseName, '$dbName.$schemaName.$tableName' AS TableName, * FROM [$schemaName].[$tableName]"
        $data = Invoke-Sqlcmd -ServerInstance $serverInstance -Database $dbName -Query $query

        if ($data) {
            # CSV 파일 저장
            $fileName = "$outputFolder\$($dbName)_$($schemaName)_$($tableName).csv"
            $data | Export-Csv -Path $fileName -NoTypeInformation
            Write-Host "Exported: $fileName"
        } else {
            Write-Host "No data for table: $schemaName.$tableName in database: $dbName"
        }
    }
}

 

[결과]

모든 DB 의 모든 테이블 TOP3 csv Export

 

[파일]

 

---

순차적으로 원하는 데이터에 가깝게 쿼리를 수정해가면서 진행을 하였고, 결과적으로 원하는 데이터를 얻었습니다.