找出table_name這個table的欄位名稱、型態(大小)、預設值、描述、isNull
Select C.COLUMN_NAME,
CASE DATA_TYPE WHEN 'nvarchar' THEN DATA_TYPE+'('+CONVERT(NVARCHAR(1000),CHARACTER_MAXIMUM_LENGTH)+')' ELSE DATA_TYPE END,
ISNULL(C.COLUMN_DEFAULT, ''), ISNULL(D.value, ''), CASE C.IS_NULLABLE WHEN 'NO' THEN '' ELSE 'Y' END,
D.NAME, D.object_id, D.minor_id,
C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
From (
Select A.name, A.object_id, B.minor_id, B.value
From sys.tables A LEFT JOIN sys.extended_properties B
on A.object_id = B.major_id
) D RIGHT JOIN Information_Schema.Columns C
ON D.name = C.TABLE_NAME AND D.minor_id = C.ORDINAL_POSITION
Where C.TABLE_NAME = 'table_name'
ORDER BY C.ORDINAL_POSITION