显示某个sql server某个数据库中所有表或视图的信息
sql server 2000 与 2005 不同 差别在于 红色字部分
以下语句为获取所有表信息，替换绿色黑体字”u”为”v”为获取所有视图信息。
sql server 2000 版本
select sysobjects.name as table_name, syscolumns.id, syscolumns.name as column_name,
systypes.name as data_type, syscolumns.length as character_maximum_length,
sysproperties.[value] as column_description, syscomments.text as
column_default,syscolumns.isnullable as is_nullable from syscolumns
inner join systypes
on syscolumns.xtype = systypes.xtype
left join sysobjects on syscolumns.id = sysobjects.id
left outer join sysproperties on
( sysproperties.smallid = syscolumns.colid
and sysproperties.id = syscolumns.id)
left outer join syscomments on syscolumns.cdefault = syscomments.id
where syscolumns.id in
(select id from sysobjects where xtype = ‘u’) and (systypes.name <> ‘sysname’)
order by syscolumns.colid
sql server 2005版本
select sysobjects.name as table_name, syscolumns.id, syscolumns.name as column_name,
systypes.name as data_type, syscolumns.length as character_maximum_length,
sys.extended_properties.[value] as column_description, syscomments.text as
column_default,syscolumns.isnullable as is_nullable from syscolumns
inner join systypes
on syscolumns.xtype = systypes.xtype
left join sysobjects on syscolumns.id = sysobjects.id
left outer join sys.extended_properties on
( sys.extended_properties.minor_id = syscolumns.colid
and sys.extended_properties.major_id = syscolumns.id)
left outer join syscomments on syscolumns.cdefault = syscomments.id
where syscolumns.id in
(select id from sysobjects where xtype = ‘u’) and (systypes.name <> ‘sysname’)
order by syscolumns.colid
参考：http://www.devx.com/tips/tip/31235?type=kbarticle&trk=mscp