显示某个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"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>