-- 테이블 목록
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
위의 정보를 이용하여 테이블 정보를 출력해 보자~
프로젝트 산출물 작업시 유용하게 쓰일것이다.
포맷이 다음과 같다면...
| 컬럼 명 |
컬럼 ID |
Type & Length |
NOT
NULL |
PK |
FK |
IDX |
비고 |
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
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
보안 정보 
