[QUERY] 테이블 이름으로 컬럼 목록 출력(Data Type, PK, FK, NOT NULL 설정까지)

테이블 이름을 기준으로 컬럼 목록 조회. 

SELECT a.TABLE_NAME as 'Table Name'
	, a.COLUMN_NAME	as 'Column Name'
	, 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;

 

결과값은 아래처럼 나온다