SELECT
表名
=
CASE
WHEN
A.COLORDER
=
1
THEN
D.NAME
ELSE
'
'
END
,
表說明
=
CASE
WHEN
A.COLORDER
=
1
THEN
ISNULL
(F.VALUE,
'
'
)
ELSE
'
'
END
,
欄位序號
=
A.COLORDER,
欄位名
=
A.NAME,
標識
=
CASE
WHEN
COLUMNPROPERTY
( A.ID,A.NAME,
'
ISIDENTITY
'
)
=
1
THEN
'
√
'
ELSE
'
'
END
,
主鍵
=
CASE
WHEN
EXISTS
(
SELECT
1
FROM
SYSOBJECTS
WHERE
XTYPE
=
'
PK
'
AND
PARENT_OBJ
=
A.ID
AND
NAME
IN
(
SELECT
NAME
FROM
SYSINDEXES
WHERE
INDID
IN
(
SELECT
INDID
FROM
SYSINDEXKEYS
WHERE
ID
=
A.ID
AND
COLID
=
A.COLID)))
THEN
'
√
'
ELSE
'
'
END
,
類型
=
B.NAME,
佔用位元組數
=
A.LENGTH,
長度
=
COLUMNPROPERTY
(A.ID,A.NAME,
'
PRECISION
'
),
小數位數
=
ISNULL
(
COLUMNPROPERTY
(A.ID,A.NAME,
'
SCALE
'
),
0
),
允許空
=
CASE
WHEN
A.ISNULLABLE
=
1
THEN
'
√
'
ELSE
'
'
END
,
預設值
=
ISNULL
(E.
TEXT
,
'
'
),
欄位說明
=
ISNULL
(G.
[
VALUE
]
,
'
'
)
FROM
SYSCOLUMNS A
LEFT
JOIN
SYSTYPES B
ON
A.XUSERTYPE
=
B.XUSERTYPE
INNER
JOIN
SYSOBJECTS D
ON
A.ID
=
D.ID
AND
D.XTYPE
=
'
U
'
AND
D.NAME
<>
'
DTPROPERTIES
'
LEFT
JOIN
SYSCOMMENTS E
ON
A.CDEFAULT
=
E.ID
LEFT
JOIN
sys.extended_properties G
ON
A.ID
=
G.major_id
AND
A.COLID
=
G.minor_id
LEFT
JOIN
sys.extended_properties F
ON
D.ID
=
F.major_id
AND
F.minor_id
=
0
--
where d.name='pa_T_FORM_FI' --如果只查询指定表,加上此条件
ORDER
BY
A.ID,A.COLORDER