728x90
아래 생성한 컬럼을 테이블 정의서 만들때 사용한다.
해당 내역을 조회해 오는 쿼리
프로시저로 생성해서 사용해도 된다.
SELECT DISTINCT A.ORDINAL_POSITION AS SEQ
, A.TABLE_CATALOG AS DB
, A.TABLE_SCHEMA AS USERID
, A.TABLE_NAME AS T_NM
, '' AS T_KOR_NM
, A.COLUMN_NAME AS C_NM
, (SELECT [Description] = ex.value
FROM sys.columns c
LEFT OUTER JOIN sys.extended_properties ex ON (ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = 'MS_Description')
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
AND OBJECT_NAME(c.object_id) = A.TABLE_NAME
AND c.name = A.COLUMN_NAME) AS C_NM_KR --> 2005이후 컬러명 조회 추후 테이블 깔끔하게 정리필요.
, A.DATA_TYPE + CASE A.DATA_TYPE
WHEN 'varchar' THEN ' ('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'
WHEN 'int' THEN ' ('+LTRIM(STR(numeric_precision))+')'
WHEN 'numeric' THEN ' ('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'
WHEN 'decimal' THEN ' ('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'
WHEN 'datetime' THEN ''
WHEN 'image' THEN ''
WHEN 'char' THEN ' ('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'
END AS TYPE
, '' AS '사용여부'
, ISNULL((SELECT 'PK'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE COLUMN_NAME = A.COLUMN_NAME
AND TABLE_NAME = A.TABLE_NAME
), '') AS C_KEY
, CASE A.IS_NULLABLE
WHEN 'NO' THEN 'NN'
WHEN 'YES' THEN ''
END AS NN
, ISNULL(A.COLUMN_DEFAULT, '') AS DFT
, ', ' + '@' + A.COLUMN_NAME + ' ' + A.DATA_TYPE + CASE A.DATA_TYPE WHEN 'varchar' THEN '('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'
WHEN 'int' THEN '('+LTRIM(STR(numeric_precision))+')'
WHEN 'numeric' THEN '('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'
WHEN 'decimal' THEN '('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'
WHEN 'datetime' THEN ''
WHEN 'image' THEN ''
WHEN 'char' THEN '('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'
END AS ARG
, ', ' + A.COLUMN_NAME AS C_NM2
, ', @' + A.COLUMN_NAME AS C_NM3
from information_schema.COLUMNS A , INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
WHERE A.TABLE_NAME = '조회할테이블명'
AND A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.ORDINAL_POSITION
728x90
'개발및업무 > SQL' 카테고리의 다른 글
| [MS-SQL] 프로시저 내용검색 (0) | 2011.05.16 |
|---|---|
| MS SQL 2005 Linked Server 연결(연결된 서버) (0) | 2011.05.09 |
| 오류 0xc00470fe: 데이터 흐름 작업: 제품 수준이 구성 요소 "원본 - Sheet1$"(1)에 부족합니다. (0) | 2010.12.01 |
| Ms-Sql 암호화, 복호화 (PwdEncrypt, PwdCompare) (0) | 2010.12.01 |
| SQL 디스크립션 생성 및 수정 방법 (0) | 2010.08.19 |