MS-SQL 테이블명세서 쿼리 공유

테이블명세서 쿼리 입니다. 

정부지원 산출물 자료에 사용하셔도 되고, 개발시 테이블스키마 참고할때 사용하면 유용하실거 같아요. 

Use 데이터베이스명
go
SELECT 
	 d.value 테이블명
	, a.TABLE_NAME 테이블ID
	, e.value 컬럼명
	, a.COLUMN_NAME	컬럼ID		
	, case when a.CHARACTER_MAXIMUM_LENGTH IS null then a.DATA_TYPE else a.DATA_TYPE + '(' + case when ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) = '-1' then 'max' else ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) end  + ')' end as DATATYPE	
	, case when a.column_name=isnull(b.column_name,'') and c.constraint_type='PRIMARY KEY' then 'Y' else ' ' end as PK
	, case when a.column_name=isnull(b.column_name,'') and c.constraint_type='FOREIGN KEY' then 'Y' else ' ' end as FK
	, case when a.IS_NULLABLE='YES' then 'Y' else '' end as NOT_NULL	
FROM INFORMATION_SCHEMA.COLUMNS a 
	LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b 
		ON (a.table_name+a.column_name=b.table_name+b.column_name) 
	LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c 
		ON (b.constraint_name=c.constraint_name) 
	LEFT JOIN ( SELECT OBJECT_ID(Objname) Table_Id,Value FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'user','dbo','Table',NULL, NULL, NULL) ) d 
		ON d.Table_id = OBJECT_ID(A.Table_Name)
	LEFT JOIN SYS.EXTENDED_PROPERTIES e 
		ON e.Major_Id = OBJECT_ID(A.Table_Name) 
		AND e.Minor_Id = A.Ordinal_Position 
WHERE a.TABLE_NAME = '테이블명'
ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION;