테이블 컬럼 코멘트 및 정보 확인 SQL

MS-SQL에서 제공하는 SSMS는 UI기반의 편한 툴이지만 테이블 정보 등 코멘트를 봐야할때는 불편한 경우가 많아

 

아래와같은 쿼리를 작성하여 사용중이다.

 

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 = '테이블명'
	ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION