1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| SELECT
c.name '欄位名稱',
ep.value AS '欄位備註',
t.Name '欄位型態',
c.max_length '最大byte',
c.precision '總位數',
c.scale '小數位數',
c.is_nullable '是否可為空',
ISNULL(i.is_primary_key, 0) '是否是PK'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
INNER JOIN sys.objects ON sys.objects.object_id = c.object_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
OUTER APPLY fn_listextendedproperty(default,
'SCHEMA', schema_name(sys.objects.schema_id),
'TABLE', sys.objects.name, 'COLUMN', c.name) ep
WHERE
c.object_id = OBJECT_ID('your table name')
|