테이블 정보 반환 쿼리

데이터베이스 설계문서 작성시 사용

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.DATA_TYPE + '(' + CONVERT( NVARCHAR, F.CHARACTER_OCTET_LENGTH ) + ')' AS DATA_TY
		  , CASE WHEN F.IS_NULLABLE = 'YES' THEN 'Y' ELSE 'N' END  AS IS_NULLABLE            -- Column Nullable
		  , G.ORDINAL_POSITION
		  , g.CONSTRAINT_NAME
		  ,CASE WHEN CHARINDEX('PK',  g.CONSTRAINT_NAME) > 0 THEN 'Y' ELSE NULL END AS PK
		  ,CASE WHEN CHARINDEX('FK',  g.CONSTRAINT_NAME) > 0 THEN 'Y' ELSE NULL END AS FK
	 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 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE G WITH (NOLOCK)
     	ON A.NAME = G.TABLE_NAME		AND
				 D.NAME = G.COLUMN_NAME
LEFT JOIN SYS.EXTENDED_PROPERTIES C WITH (NOLOCK)
     	ON C.MAJOR_ID = A.ID		AND
				 C.MINOR_ID = 0			AND
				 C.NAME = 'MS_Description'
LEFT 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 A.NAME = '테이블명'   		AND 
				A.TYPE = 'U'
ORDER BY A.NAME, 
				 D.COLORDER