[DB] MSSQL 기본 쿼리 (테이블, 컬럼, 인덱스, 프로시저, DB Lock 조회)

7여년동안 Oracle만 사용하다 MS SQL를 사용한지 약 1년 정도 시점에 저와 같이 MSSQL이 적응되지 않는 개발자들을 위해 MSSQL 조회 기본쿼리에 대해 소개하고자 합니다.

 

데이터베이스의 모든 테이블 및 열에 대한 정보 조회 

 

SELECT

    t.TABLE_SCHEMA AS 'Schema',
    t.TABLE_NAME AS 'Table',
    c.COLUMN_NAME AS 'Column',
    c.DATA_TYPE AS 'Data Type',
    c.CHARACTER_MAXIMUM_LENGTH AS 'Max Length',
    c.IS_NULLABLE AS 'Nullable'
FROM
    INFORMATION_SCHEMA.TABLES t
INNER JOIN
    INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE
    t.TABLE_TYPE = 'BASE TABLE'
-- Target 테이블
-- AND t.TABLE_NAME = 'TB_SYS_ERROR_LOG'
ORDER BY
    t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION;

 

이 쿼리는 INFORMATION_SCHEMA 뷰를 사용하여 테이블과 열의 메타데이터를 검색합니다. 결과는 각 열의 이름, 데이터 유형, 최대 길이 및 널 가능 여부 등을 포함하여 테이블과 관련된 모든 정보를 나열합니다.

데이터베이스 스키마를 자세히 조사하려면 이 쿼리를 사용하여 데이터베이스에 대한 전반적인 이해를 얻을 수 있습니다. 

 

인덱스와 해당 인덱스에 포함된 각 컬럼 목록을 함께 표시 

 

SELECT
    OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName,
    STUFF((SELECT ', ' + COL_NAME(ic.OBJECT_ID,ic.column_id)
           FROM sys.index_columns ic
           WHERE ic.index_id = ind.index_id
               AND ic.OBJECT_ID = ind.OBJECT_ID
           FOR XML PATH('')),1,2,'') AS IndexColumns
FROM sys.indexes ind
WHERE


    OBJECT_NAME(ind.OBJECT_ID) = 'TB_APP_MSAT'

 

DB Lock 조회 및 모니터링

 

SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED > 1

 

이 쿼리는 sys.sysprocesses 시스템 뷰를 사용하여 현재 실행 중인 모든 프로세스를 검색하고, 'blocked' 열의 값이 1보다 큰 경우에 해당하는 프로세스를 반환합니다.

이는 블록된 프로세스를 식별하는 데 사용됩니다. 이러한 프로세스는 다른 프로세스에 의해 차단되어 실행이 지연되고 있는 것을 나타냅니다.

 

프로시저 내용 조회

 

SELECT A.NAME, A.CRDATE, A.REFDATE, B.TEXT
FROM SYS.SYSOBJECTS AS A WITH (NOLOCK)
INNER JOIN SYS.SYSCOMMENTS AS B WITH (NOLOCK)
    ON A.ID = B.ID
WHERE A.XTYPE = 'P';

 

이 쿼리는 시스템 뷰를 사용하여 데이터베이스 내에 있는 프로시저(Stored Procedure)의 정보를 검색합니다.

SYSOBJECTS 뷰는 데이터베이스 객체의 메타데이터를 제공하고, SYSCOMMENTS 뷰는 데이터베이스 객체의 주석에 대한 정보를 제공합니다.

따라서 이 쿼리는 프로시저의 이름, 생성일자, 참조일자 및 해당 프로시저의 소스 코드를 반환합니다.

 

프로시저 내 텍스트 조회

 

SELECT A.NAME, A.CRDATE, A.REFDATE, B.TEXT
FROM SYS.SYSOBJECTS AS A WITH (NOLOCK)
INNER JOIN SYS.SYSCOMMENTS AS B WITH (NOLOCK)
    ON A.ID = B.ID
WHERE A.XTYPE = 'P'
AND B.TEXT LIKE '%SEARCH_TEXT%';

 

이 쿼리는 SYSOBJECTS와 SYSCOMMENTS 시스템 뷰를 사용하여 데이터베이스 내에 있는 프로시저의 메타데이터와 소스 코드를 검색합니다.

WHERE 절에서 B.TEXT LIKE 구문을 사용하여 프로시저 소스 코드에서 'SEARCH_TEXT'를 포함하는지 확인합니다. 이러한 검색을 통해 원하는 문자열을 포함하는 프로시저를 식별할 수 있습니다.