Sql语句:显示SqlServer中所有表中的信息

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

显示某个SqlServer某个中所有表或视图的信息
Sqlserver2000与2005不同差别在于红色字部分
以下语句为获取所有表信息,替换绿色黑体字"U"为"V"为获取所有视图信息

SqlServer2000版本

SELECTSysobjects.nameASTABLE_NAME,syscolumns.Id,syscolumns.nameASCOLUMN_NAME,
systypes.nameASDATA_TYPE,syscolumns.lengthasCHARACTER_MAXIMUM_LENGTH,
sysproperties.[value]ASCOLUMN_DESCRIPTION, syscomments.textas
COLUMN_DEFAULT,syscolumns.isnullableasIS_NULLABLEFROMsyscolumns
INNERJOINsystypes
   ONsyscolumns.xtype=systypes.xtype
   LEFTJOINsysobjectsONsyscolumns.id=sysobjects.id
  LEFTOUTERJOINsyspropertiesON
  (sysproperties.smallid=syscolumns.colid
    ANDsysproperties.id=syscolumns.id)
  LEFTOUTERJOINsyscommentsONsyscolumns.cdefault=syscomments.id
  WHEREsyscolumns.idIN 
   (SELECTidFROMSYSOBJECTSWHERExtype='U')AND(systypes.name<>'sysname')
   ORDERBYsyscolumns.colid

SqlServer2005版本

SELECTSysobjects.nameASTABLE_NAME,syscolumns.Id,syscolumns.nameASCOLUMN_NAME,
systypes.nameASDATA_TYPE,syscolumns.lengthasCHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value]ASCOLUMN_DESCRIPTION, syscomments.textas
COLUMN_DEFAULT,syscolumns.isnullableasIS_NULLABLEFROMsyscolumns
INNERJOINsystypes
   ONsyscolumns.xtype=systypes.xtype
   LEFTJOINsysobjectsONsyscolumns.id=sysobjects.id
  LEFTOUTERJOINsys.extended_propertiesON
  (sys.extended_properties.minor_id=syscolumns.colid
    ANDsys.extended_properties.major_id=syscolumns.id)
  LEFTOUTERJOINsyscommentsONsyscolumns.cdefault=syscomments.id
  WHEREsyscolumns.idIN 
   (SELECTidFROMSYSOBJECTSWHERExtype='U')AND(systypes.name<>'sysname')
   ORDERBYsyscolumns.colid


<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: sqlserver sql
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭