1
select
2 (
case
when
a.colorder
=
1
then
d.name
else
''
end
) 表名,
3 a.colorder 字段序号,
4 a.name 字段名,
5 (
case
when
COLUMNPROPERTY
(a.id,a.name,
'
isidentity
'
)
=
1
then
'
√
'
else
''
end
) 标识,
6 (
case
when
(
7
select
count
(
*
)
from
sysobjects
8
where
name
in
(
9
select
name
from
sysindexes
10
where
(id
=
a.id )
and
( indid
in
11 (
select
indid
from
sysindexkeys
where
12 ( id
=
a.id )
and
( colid
in
(
13
select
colid
from
syscolumns
14
where
( id
=
a.id )
and
( name
=
a.name ))))))
15
and
( xtype
=
'
PK
'
))
>
0
then
'
√
'
else
''
end
) 主键,
16 b.name 类型,
17 a.length 字节数,
18
COLUMNPROPERTY
( a.id,a.name ,
'
PRECISION
'
)
as
长度,
19
isnull
(
COLUMNPROPERTY
( a.id,a.name ,
'
Scale
'
),
0
)
as
小数位数,
20 (
case
when
a.isnullable
=
1
then
'
√
'
else
''
end
) 允许空,
21
isnull
( e.
text
,
''
) 默认值,
22
isnull
(g.
[
value
]
,
''
)
as
字段说明
23
from
syscolumns a
left
join
systypes b
24
on
a.xtype
=
b.xusertype
25
inner
join
sysobjects d
26
on
a.id
=
d.id
and
d.xtype
=
'
U
'
and
d.name
<>
'
dtproperties
'
27
left
join
syscomments e
28
on
a.cdefault
=
e.id
29
left
join
sysproperties g
30
on
a.id
=
g.id
and
a.colid
=
g.smallid
31
order
by
a.id ,a.colorder
select
2
(
case
when
a.colorder
=
1
then
d.name
else
''
end
) 表名,3
a.colorder 字段序号,4
a.name 字段名,5
(
case
when
COLUMNPROPERTY
(a.id,a.name,
'
isidentity
'
)
=
1
then
'
√
'
else
''
end
) 标识,6
(
case
when
( 7
select
count
(
*
)
from
sysobjects8
where
name
in
(9
select
name
from
sysindexes10
where
(id
=
a.id )
and
( indid
in
11
(
select
indid
from
sysindexkeys
where
12
( id
=
a.id )
and
( colid
in
(13
select
colid
from
syscolumns14
where
( id
=
a.id )
and
( name
=
a.name ))))))15
and
( xtype
=
'
PK
'
))
>
0
then
'
√
'
else
''
end
) 主键,16
b.name 类型,17
a.length 字节数,18
COLUMNPROPERTY
( a.id,a.name ,
'
PRECISION
'
)
as
长度,19
isnull
(
COLUMNPROPERTY
( a.id,a.name ,
'
Scale
'
),
0
)
as
小数位数,20
(
case
when
a.isnullable
=
1
then
'
√
'
else
''
end
) 允许空,21
isnull
( e.
text
,
''
) 默认值,22
isnull
(g.
[
value
]
,
''
)
as
字段说明23
from
syscolumns a
left
join
systypes b24
on
a.xtype
=
b.xusertype25
inner
join
sysobjects d26
on
a.id
=
d.id
and
d.xtype
=
'
U
'
and
d.name
<>
'
dtproperties
'
27
left
join
syscomments e28
on
a.cdefault
=
e.id29
left
join
sysproperties g30
on
a.id
=
g.id
and
a.colid
=
g.smallid31
order
by
a.id ,a.colorder
从这个 sql 语句我学到的东西：
1、case when ... then ... else ... end ：选择语句用在 select 语句中，可以将原来用0,1这样的描述信息，转换为实际的含义，而不要在程序中根据查询出来的结果再进行判断。这个可以理解为简单的数据格式化吧。如这条sql 语句中出现的 case when a.isnullable = 1 then '√' else '' end 将数据库从存储的 0,1 转换为了 '√' 和''；
2、left join ：使用这种连接方式可以使查询结果描述出一种包含关系。
3、isnull 函数：ISNULL ( check _ expression , replacement_value ) ,作用是使用指定的替换值替换 NULL，例如下面的 SQL 语句中如果一本书的名称为 null ，则将价格设置为 0.00。
1
SELECT
SUBSTRING
(title,
1
,
15
)
AS
Title, type
AS
Type,
2
ISNULL
(price,
0.00
)
AS
Price
3
FROM
titles
4
SELECT
SUBSTRING
(title,
1
,
15
)
AS
Title, type
AS
Type, 2
ISNULL
(price,
0.00
)
AS
Price3
FROM
titles4
4、当然，最重要的是学到这个列出数据库表信息（包括表名、字段名、是否标识、是否主键、字段类型、字节数、长度、小数位数、允许空、默认值、字段说明）的 SQL 语句。^_^
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
