--
-------SQL SERVER ---------------
--
--- 獲取用戶表
select
id,name
from
sysobjects
where
xtype
=
'
U
'
and
name
<>
'
dtproperties
'
order
by
name
--
-- 獲取用戶存儲過程
select
id,name
from
sysobjects
where
xtype
=
'
P
'
and
name
<>
'
dtproperties
'
order
by
name
--
- 獲取用戶視圖
select
id,name
from
sysobjects
where
xtype
=
'
V
'
and
name
<>
'
dtproperties
'
order
by
name
declare
@TbName
nvarchar
(
60
)
go
set
@TbName
=
'
tbname
'
--
---獲取用戶表具體結構
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
=
@TbName
ORDER
BY
A.ID,A.COLORDER
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
