2022. 1. 11. 13:07ㆍIT/MS-SQL
SELECT D.COLORDER AS COLUMN_IDX -- Column Index
, A.NAME AS TABLE_NAME -- Table Name
, C.VALUE AS TABLE_DESCRIPTION -- Table Description
, D.NAME AS COLUMN_NAME -- Column Name
, E.VALUE AS COLUMN_DESCRIPTION -- Column Description
, F.DATA_TYPE AS TYPE -- Column Type
, F.CHARACTER_OCTET_LENGTH AS LENGTH -- Column Length
, F.IS_NULLABLE AS IS_NULLABLE -- Column Nullable
, F.COLLATION_NAME AS COLLATION_NAME -- Column Collaction Name
FROM SYSOBJECTS A WITH (NOLOCK)
INNER JOIN SYSUSERS B WITH (NOLOCK) ON A.UID = B.UID
INNER JOIN SYSCOLUMNS D WITH (NOLOCK) ON D.ID = A.ID
INNER JOIN INFORMATION_SCHEMA.COLUMNS F WITH (NOLOCK)
ON A.NAME = F.TABLE_NAME
AND D.NAME = F.COLUMN_NAME
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES C WITH (NOLOCK)
ON C.MAJOR_ID = A.ID
AND C.MINOR_ID = 0
AND C.NAME = 'MS_Description'
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E WITH (NOLOCK)
ON E.MAJOR_ID = D.ID
AND E.MINOR_ID = D.COLID
AND E.NAME = 'MS_Description'
WHERE 1=1
AND A.TYPE = 'U'
ORDER BY D.COLORDER
## MSSQL 테이블 컬럼 정보 및 MS DESC 가져오기
SELECT *
FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DB', '테이블', '테이블명', DEFAULT, DEFAULT)
'IT > MS-SQL ' 카테고리의 다른 글
"Chinese_PRC_CI_AS"과(와) "Korean_Wansung_CI_AS" 간의 데이터 정렬 충돌을 해결할 수 없습니다. (0) | 2023.09.13 |
---|---|
sqlca.sqlcode 값 정리 0 1 100 -1 -2 (0) | 2023.09.06 |
MSSQL TABLE INDEX SEARCH 테이블 인덱스 조회 (0) | 2022.01.07 |
MSSQL 인덱스 조각 모음 쿼리 (0) | 2022.01.07 |
[MSSQL] 가장 조각이 많이 난 인덱스 TOP 10 (0) | 2022.01.04 |