--
-------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
