[mssql] 테이블 스키마 정보, 코멘트 조회 (show table schema fields or comment)

 SELECT 

 A.TABLE_NAME, 

 C.VALUE AS TABLE_COMMENT, 

 A.COLUMN_NAME, A.DATA_TYPE, 

 ISNULL( 

 CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), 

 CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + 

 CAST(A.NUMERIC_SCALE AS VARCHAR) 

 ) AS COLUMN_LENGTH, 

 A.COLUMN_DEFAULT, A.IS_NULLABLE, 

 B.VALUE AS COLUM_COMMENT 

 FROM INFORMATION_SCHEMA.COLUMNS A 

 LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B ON B.major_id = object_id(A.TABLE_NAME) 

 AND A.ORDINAL_POSITION = B.minor_id 

 LEFT OUTER JOIN 

 ( 

 SELECT object_id(objname) AS TABLE_ID, VALUE 

 FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table',NULL, NULL, NULL) 

 ) C ON object_id(A.TABLE_NAME) = C.TABLE_ID 

 WHERE A.TABLE_NAME IN ( 

 

 /* 여기에 테이블명 입력 (Enter table name here) */ 

 'BM_product', 

 'BM_Basket', 

 'BM_PurchaseMaster', 

 'BM_PurchaseSub', 

 'BM_PurchaseSubStateLog', 

 'BM_Category', 

 'BM_product' 

 

 )

 ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION 

 ;