-------------------------------------------------------------------------------获取SQL Server中的所有数据库，数据表，列，备注说明在MS SQL Server中，可通过如下语句查询相关系统信息，如：使用：Select name from sysobjects where xtype=’U’;可得到所有用户表的名称；------------------------------------Select name from sysobjects where xtype=’S’;得到所有系统表的名称；相应的，使用：Select count(*)-1 from sysobjects where xtype=’U’;得到用户表的张数。

上面之所以要减1,是因为在SQLServer2000中,有一系统表:dtproperties被标记为了用户表,这或许是SQLServer2000中的一个BUG,在2005中,就不存在该表了,而是用表:sysdiagram代替,该表是用来存储数据关系图可通过：Select @@version;查询数据库的版本------------------------------------------------在SQL Server2000下sysproperties表中的type=3表示当前的对象是"表",type=4表示是"字段"在SQL Server2005下sys.extended_properties表中的minor_id=0表示当前的对象是"表",minor_id> 0表示是"字段"可通过如下语句提取用户表的描述或列的描述，该描述全放在一个叫做sysproperties的系统表中select , sysproperties.Valuefrom sysproperties,sysobjectswheresysproperties.id=sysobjects.idand ='MS_Description'and sysproperties.type='3'order by 在该表中，id 列与sysobjects中的id列是对应的，当该表的type值为３时，是对表的描述，为４，是对列的描述……………………………………………………………………………………………………--1.获取所有数据库名:SELECT Name FROM Master..SysDatabases ORDER BY Name--2.获取所有表名:SELECT Name FROM ..SysObjects Where XType='U' ORDER BY Name--XType='U':表示所有用户表;--XType='S':表示所有系统表;--3.获取所有字段名及说明:（Server2000）SELECT ,sysproperties.[value] AS CommentFROM sysproperties INNER JOIN sysobjects ON sysproperties.id = sysobjects.idINNER JOIN syscolumns ON sysobjects.id = syscolumns.id AND sysproperties.smallid = syscolumns.colidWHERE (sysproperties.type = 4) AND ( = 'message')--------------------------------------------------------------------------------------获取表中字段信息(主外键，字段名，数据类型，字段长度，列说明)select(case when PKeyCol.COLUMN_NAME is null then '' else 'PK' end) + (case when KeyCol2.COLUMN_NAME is null then ''when NOT PKeyCol.COLUMN_NAME is null then ',FK' else 'FK' end) as [主/外键],col.COLUMN_NAME as 字段名称,DATA_TYPE as DataType,(case when CHARACTER_MAXIMUM_LENGTH is null then '' elseCAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as 字段长度,--(case when coldesc.value is null then '' else coldesc.value end) AS 字段说明,*ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS 字段说明from INFORMATION_SCHEMA.COLUMNS as colLEFT OUTER JOIN(select COLUMN_NAME,TABLE_NAME FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColLEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG ANDKeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAMEWHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyColON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME ANDPKeyCol.TABLE_NAME=Col.TABLE_NAMELEFT OUTER JOIN(INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON KeyCol2.CONSTRAINT_CATALOG=RefCol2.CONSTRAINT_CATALOG ANDKeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME ANDKeyCol2.TABLE_NAME=Col.TABLE_NAMELEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', "+tableName+", 'column', default) as coldescON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_ASwhere col.TABLE_NAME="+tableName+"将"+tableName+"换成你要查询的表名就可以了--------------------------------------------获取表中所有信息,没有字段说明exec sp_columns " + tableName + "----------------------------------------------获取表中主键的字段select COLUMN_NAME AS KeyName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME WHERERefCol.CONSTRAINT_NAME IS NULL AND KeyCol.TABLE_NAME="+tableName+"-------------------------------------------------------获取表中字段的信息（列名称，数据类型，长度，字段说明）select col.COLUMN_NAME as FieldName,DATA_TYPE as DataType,(case when CHARACTER_MAXIMUM_LENGTH is null then '' elseCAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) asFieldLength,ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS FieldExplain from INFORMATION_SCHEMA.COLUMNS as col LEFT OUTERJOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', " + tableName +", 'column', default) as coldesc ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME=" +tableName + "----------------------------------获取数据库文件信息：select*from sys.database_files----------------------------------------------可以使用内置的存储过程sp_MShelpcolumns查询表的结构。