mssql 쿼리명세서 생성

프로젝트 마무리때 주로 쓰게된다.

 

SELECT 
	A.Table_Name,
	C.Value Table_Description, 
	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)
	) Column_Length, 
	A.Column_Default, 
	CASE WHEN A.Is_Nullable = 'YES' THEN 'Y' ELSE 'N' END Is_Nullable, 
	B.value 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

 

출처 : rocabilly.tistory.com/60