티스토리 뷰

프로그래밍/DB

mssql 쿼리명세서 생성

LunaStratos 2020. 11. 16. 15:59

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

 

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

공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함