在MS SQL Server 2000中查找一个数据库中的所有用户表和用户视图的系统ID、名称和其注释信息的SQL语句:
SELECT
( case when a.colorder = 1 then d.name else '' end ) 表名,
a.colorder 字段序号,
a.name 字段名,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) 标识,
( 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 占用字节数,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as 长度,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as 小数位数,
( case when a.isnullable = 1 then ' √ ' else '' end ) 允许空,
isnull (e. text , '' ) 默认值,
isnull (g. [ value ] , '' ) AS 字段说明
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 sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by a.id,a.colorder
( case when a.colorder = 1 then d.name else '' end ) 表名,
a.colorder 字段序号,
a.name 字段名,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) 标识,
( 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 占用字节数,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as 长度,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as 小数位数,
( case when a.isnullable = 1 then ' √ ' else '' end ) 允许空,
isnull (e. text , '' ) 默认值,
isnull (g. [ value ] , '' ) AS 字段说明
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 sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by a.id,a.colorder