컴퓨토피아

-- 테이블 목록
EXEC SP_TABLES

-- 테이블이 컬럼 목록

EXEC SP_COLUMNS @TABLE_NAME

-- 스키마 테이블에서 직접 컬럼정보 가져오기~

SELECT  *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME

-- PK, FK, INDEX등 정보 가져오기

SELECT  *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE K.TABLE_NAME = @TABLE_NAME

-- 컬럼에 추가해둔 Description 가져오기

SELECT  *
FROM SYS.EXTENDED_PROPERTIES XP 
WHERE XP.CLASS = 1
 AND  XP.MINOR_ID > 0
 AND XP.MAJOR_ID = OBJECT_ID(@TABLE_NAME) 
 AND XP.NAME IN (N'MS_DESCRIPTION') 
ORDER BY XP.MINOR_ID

위의 정보를 이용하여 테이블 정보를 출력해 보자~

프로젝트 산출물 작업시 유용하게 쓰일것이다.

포맷이 다음과 같다면...

DECLARE @TABLE_NAME AS VARCHAR(30)

SET @TABLE_NAME = 'TEM_BOARD'

SELECT

         [컬럼명] = D.COLUMN_DESC

       , [컬럼ID] = C.COLUMN_NAME

       , [Type & Length] = COLUMN_TYPE

       , [NOT NULL] = CASE WHEN C.IS_NULLABLE = 'NO' THEN 'Y' ELSE '' END

       , PK = ISNULL(K.PK, '')

       , FK = ISNULL(K.FK, '')

       , INX = ISNULL(K.INX, '')

FROM

(

 SELECT 

    TABLE_NAME

  , COLUMN_NAME

  , IS_NULLABLE

  , COLUMN_TYPE = CASE WHEN DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar') THEN

       DATA_TYPE + '(' + CONVERT(VARCHAR(10), ISNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION)) + ')'

      WHEN DATA_TYPE IN ('DECIMAL') THEN

       DATA_TYPE + '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'      

      ELSE DATA_TYPE END

  , ORDINAL_POSITION

 FROM INFORMATION_SCHEMA.COLUMNS

 WHERE TABLE_NAME = @TABLE_NAME

) AS C

LEFT JOIN -- 키정보

(

       SELECT COLUMN_NAME

             , PK = MAX(PK)

             , FK = MAX(FK)

             , INX = MAX(INX)

       FROM

       (

             SELECT

                      COLUMN_NAME

                    , PK = CASE WHEN K.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'Y' END

                    , FK = CASE WHEN K.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'Y' END

                    , INX = CASE WHEN K.CONSTRAINT_TYPE = 'INDEX' THEN 'Y' END

              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K

                    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME

              WHERE K.TABLE_NAME = @TABLE_NAME

       ) AS K

       GROUP BY COLUMN_NAME

) AS K ON C.COLUMN_NAME = K.COLUMN_NAME

LEFT JOIN -- Description 정보

(

 SELECT

    COLUMN_NM = COL_NAME(XP.MAJOR_ID, XP.MINOR_ID) 

  , COLUMN_DESC = CAST(XP.[VALUE] AS NVARCHAR(4000))

 FROM SYS.EXTENDED_PROPERTIES XP 

 WHERE XP.CLASS = 1

  AND  XP.MINOR_ID > 0

  AND XP.MAJOR_ID = OBJECT_ID(@TABLE_NAME) 

  AND XP.NAME IN (N'MS_DESCRIPTION') 

) AS D ON C.COLUMN_NAME = D.COLUMN_NM

ORDER BY C.ORDINAL_POSITION