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
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
从这个 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![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
4、当然,最重要的是学到这个列出数据库表信息(包括表名、字段名、是否标识、是否主键、字段类型、字节数、长度、小数位数、允许空、默认值、字段说明)的 SQL 语句。^_^