[MS-SQL] 테이블 정보 조회

안녕하세요.

쿼리 작업을 진행 할 때, 갑자기 테이블에 대한 정보를 기억하지 못할때 없으신가요?

간단한 단축키를 통하여, 해당 테이블에 대한 내용과 인덱스의 정보를 조회 할 수있는 방법을 알려드리겠습니다.

적용 사용툴은 Microsoft SQL Server Mangment Studio 입니다.

반응형

1. 프로시저 생성

테이블의 정보와 인덱스의 정보를 조회 할 수있는 프로시저를 작성합니다.

프로시저에서 위에 값은 테이블의 정보이고, 아래의 정보는 인덱스의 정보입니다.

CREATE PROCEDURE [dbo].[TABLE_INFO]
(
    @IN_NAME            VARCHAR(MAX)                    -- 검색 값
)
AS
BEGIN 
    -- 테이블 컬럼 정보
    SELECT T01.NAME                                     					AS "TABLE NAME"         -- 테이블 명
         , CAST(L01.VALUE AS NVARCHAR(MAX))             					AS "TABLE COMMENT"      -- 테이블 주석
         , T02.COLUMN_ID                                                    AS "SEQ"                -- 컬럼 순서
         , T02.NAME                                                         AS "COLUMN NAME"        -- 컬럼 명
         , UPPER(TYPE_NAME(T02.USER_TYPE_ID))
         + CASE WHEN TYPE_NAME(T02.USER_TYPE_ID) IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR')
                THEN '(' + CAST(T02.MAX_LENGTH AS VARCHAR) + ')'
                WHEN TYPE_NAME(T02.USER_TYPE_ID) IN ('DECIMAL', 'NUMERIC')
                THEN '(' + CAST(T02.PRECISION AS VARCHAR) + ',' + CAST(T02.SCALE AS VARCHAR) + ')'
                ELSE '' END                                                 AS "DATA TYPE"          -- 데이터 타입
         , CAST(L02.VALUE AS NVARCHAR(MAX))                                 AS "COMMENT"            -- 컬럼 주석
         , CASE WHEN T05.COLUMN_ID IS NOT NULL THEN 'PK' ELSE '' END        AS "PK"                 -- PK 유무
         , CASE WHEN T02.IS_NULLABLE = 1 THEN ' ' ELSE 'N'  END             AS "NULL?"              -- NOT NULL 유무
         , ISNULL(REPLACE(REPLACE(T03.DEFINITION, '(', ''), ')', ''), '')   AS "DEFAULT"            -- 기본 값
      FROM SYS.OBJECTS T01
           LEFT  JOIN SYS.EXTENDED_PROPERTIES L01 ON L01.MAJOR_ID = T01.OBJECT_ID AND L01.MINOR_ID = 0 AND L01.NAME = 'MS_DESCRIPTION'
           LEFT  JOIN SYS.COLUMNS T02 ON T02.OBJECT_ID = T01.OBJECT_ID
           LEFT  JOIN SYS.EXTENDED_PROPERTIES L02 ON L02.MAJOR_ID = T02.OBJECT_ID AND L02.MINOR_ID = T02.COLUMN_ID AND L02.NAME = 'MS_DESCRIPTION'
           LEFT  JOIN SYS.DEFAULT_CONSTRAINTS T03 ON T03.PARENT_OBJECT_ID = T02.OBJECT_ID AND T03.PARENT_COLUMN_ID = T02.COLUMN_ID  
           LEFT  JOIN SYS.INDEXES       T04 ON T04.OBJECT_ID = T02.OBJECT_ID AND T04.TYPE = 1
           LEFT  JOIN SYS.INDEX_COLUMNS T05 ON T05.INDEX_ID = T04.INDEX_ID AND T05.OBJECT_ID = T04.OBJECT_ID AND T05.COLUMN_ID = T02.COLUMN_ID  
     WHERE 1=1
       AND T01.NAME = @IN_NAME
     ORDER BY T02.COLUMN_ID;
    
    -- 테이블 인덱스 정보
    WITH T_INDEX AS
    (
        SELECT T02.INDEX_ID                                 AS "INDEX ID"
             , T02.NAME                                     AS "INDEX NAME"
             , CASE WHEN T02.TYPE = 0 THEN 'HEAP INDEX'  
                    WHEN T02.TYPE = 1 THEN 'PK INDEX'  
                    ELSE CASE WHEN T02.IS_UNIQUE = 1 
                              THEN 'UNIQUE INDEX' 
                              ELSE 'NON-UNIQUE INDEX' 
               END       END                                AS "INDEX TYPE"
             , COL_NAME(T02.OBJECT_ID, T03.COLUMN_ID) 
             + CASE WHEN T03.IS_DESCENDING_KEY = 0 
                    THEN '' ELSE ' (DESC)' END              AS "COLUMN NAME"
          FROM SYS.OBJECTS T01
               INNER JOIN SYS.INDEXES T02 ON T02.OBJECT_ID = T01.OBJECT_ID
               INNER JOIN SYS.INDEX_COLUMNS T03 ON T03.INDEX_ID = T02.INDEX_ID AND T03.OBJECT_ID = T02.OBJECT_ID  
         WHERE T01.NAME = @IN_NAME
    )
    SELECT T01."INDEX ID"                                   AS "INDEX ID"       -- 인덱스 ID
         , T01."INDEX NAME"                                 AS "INDEX NAME"     -- 인덱스 명
         , T01."INDEX TYPE"                                 AS "INDEX TYPE"     -- 인덱스 타입
         , STUFF((SELECT ', ' + X."COLUMN NAME"
                    FROM T_INDEX X
                   WHERE X."INDEX NAME" = T01."INDEX NAME"
                     FOR XML PATH('')
	       ), 1, 1, '')                                     AS "COLUMN NAME"    -- 인덱스 컬럼
      FROM T_INDEX T01
     GROUP BY T01."INDEX ID", T01."INDEX NAME", T01."INDEX TYPE"
     ORDER BY T01."INDEX ID";
END

프로시저를 생성하였으면 다음으로 옵션 셋팅입니다.

2. 단축키 셋팅

먼저, 도구 - 옵션을 선택합니다. 다음으로 환경 - 키보드 - 쿼리바로 가기로 들어가 사용하고자 하는 단축키에 EXEC TABLE_INFO 를 입력합니다. 저는 (Ctrl + 3) 에 지정하였습니다. 그리고 (Ctrl + 4)에 SELECT * FROM 을 입력하였습니다. 

환경 - 키보드 - 쿼리 바로 가기 설정

그리고 Microsoft SQL Server Mangment Studio를 재실행 해주셔야 적용이 됩니다.

3. 실행결과

해당 테이블 명을 블럭을 지정 후 (Ctrl + 3)을 실행 하면 아래와 같이 결과가 나옵니다.

실행 결과