테이블 명세서 쿼리문

use [DB명];

SELECT a.TABLE_NAME, a.COLUMN_NAME, '' as description, 
		case when a.CHARACTER_MAXIMUM_LENGTH IS null then a.DATA_TYPE else a.DATA_TYPE + '(' + ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) + ')' 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,
        case when a.COLUMN_DEFAULT=isnull(a.COLUMN_DEFAULT,'') then a.COLUMN_DEFAULT else ' ' end as DefaultValue
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)
ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION;