MSSQL 테이블 목록/명세 보기

급하게 짜깁기를 하였다.

프로시저와 함수는 INFORMATION_SCHEMA.ROUTINES를 조회한다.

 

SELECT A.TABLE_NAME
	,A.TABLE_TYPE
	,B.VALUE AS DESCRIPTION
FROM INFORMATION_SCHEMA.TABLES A
LEFT OUTER JOIN (
	SELECT OBJECT_ID(OBJNAME) TABLE_ID
		,VALUE
	FROM::FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'Table', NULL, NULL, NULL)
	) B ON B.TABLE_ID = OBJECT_ID(A.TABLE_NAME)
ORDER BY A.TABLE_NAME

 

SELECT 
	ROW_NUMBER() OVER (ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION) AS RN,
	A.COLUMN_NAME, 
	UPPER(A.DATA_TYPE) AS DATA_TYPE, 
	ISNULL(
		CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR)
	) COLUMN_LENGTH, 
	A.COLUMN_DEFAULT, 
	CASE WHEN A.IS_NULLABLE = 'YES' THEN 'Y' ELSE 'N' END IS_NULLABLE, 
	CONVERT(VARCHAR(300),B.VALUE) AS COLUM_DESCRIPTION 
FROM INFORMATION_SCHEMA.COLUMNS A 
	LEFT JOIN SYS.EXTENDED_PROPERTIES B 
		ON B.MAJOR_ID = OBJECT_ID(A.TABLE_NAME) 
		AND B.MINOR_ID = A.ORDINAL_POSITION 
	LEFT JOIN ( SELECT OBJECT_ID(OBJNAME) TABLE_ID,VALUE FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'user','dbo','Table',NULL, NULL, NULL) ) c 
		ON C.TABLE_ID = OBJECT_ID(A.TABLE_NAME)
WHERE A.TABLE_NAME = '테이블명' 
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION