——直接放在需要查詢的庫(kù)中執(zhí)行即可。
SELECT (
case
when a.colorder=
1
then d.name
else
null
end) 表名,
a.colorder 字段序號(hào),a.name 字段名,
(
case
when COLUMNPROPERTY( a.id,a.name,
'
IsIdentity
'
)=
1
then
'
√
'
else
''
end) 標(biāo)識(shí),
(
case
when (SELECT count(*
) FROM sysobjects
WHERE (name
in
(SELECT name FROM sysindexes
WHERE (id
= a.id) AND (indid
in
(SELECT indid FROM sysindexkeys
WHERE (id
= a.id) AND (colid
in
(SELECT colid FROM syscolumns WHERE (id
= a.id) AND (name =
a.name)))))))
AND (xtype
=
'
PK
'
))>
0
then
'
√
'
else
''
end) 主鍵,b.name 類型,a.length 占用字節(jié)數(shù),
COLUMNPROPERTY(a.id,a.name,
'
PRECISION
'
)
as
長(zhǎng)度,
isnull(COLUMNPROPERTY(a.id,a.name,
'
Scale
'
),
0
)
as
小數(shù)位數(shù),(
case
when a.isnullable=
1
then
'
√
'
else
''
end) 允許空,
isnull(e.text,
''
) 默認(rèn)值,isnull(g.[value],
'
'
) AS [說(shuō)明]
FROM syscolumns a
left join systypes b on a.xtype
=
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.
class
and f.minor_id=
0
where
b.name
is
not
null
--WHERE d.name=
'
要查詢的表
'
--
如果只查詢指定表,加上此條件
order by a.id,a.colorder
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
