将SQLServer中所有表的列信息显示出来计算机等级考试

文章作者 100test 发表时间 2009:10:07 17:58:17
来源 100Test.Com百考试题网

　　将SQL Server中所有表的列信息显示出来:
　　SELECT SysObjects.Name as tb_name, SysColumns.Name as col_name,
　　SysTypes.Name as col_type, SysColumns.Length as col_len, isnull
　　(SysProperties.Value,SysColumns.Name) as col_memo,
　　case when SysColumns.name in
　　(0select 主键=a.name来源：考试大
　　FROM syscolumns a
　　inner join sysobjects b on a.id=b.id and
　　b.xtype=U and b.name<.>.dtproperties
　　where exists(SELECT 1 FROM sysobjects where xtype=PK and name in (
　　SELECT name FROM sysindexes WHERE indid in(
　　SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
　　)))
　　and b.name=SysObjects.Name
　　)
　　then 1 else 0 end as is_key
　　FROM SysObjects,SysTypes,SysColumns
　　LEFT JOIN SysProperties ON (Syscolumns.Id = Sysproperties.Id AND
　　Syscolumns.Colid = Sysproperties.Smallid)
　　WHERE (Sysobjects.Xtype =u OR Sysobjects.Xtype =v)
　　AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType
　　AND SysTypes.Name <.>. sysname AND Sysobjects.
　　Name Like % ORDER By SysObjects.Name, SysColumns.colid

　　编辑特别推荐:

　　·计算机三级的四个类别看看你适合哪个

　　·全国计算机等考三级数据库模拟试题及参考答案

　　·全国计算机等级考试三级数据库技术训练题