《sql语句获取数据库中的数据库表列》由会员分享，可在线阅读，更多相关《sql语句获取数据库中的数据库表列（5页珍藏版）》请在金锄头文库上搜索。
1、文档供参考，可复制、编制，期待您的好评与关注！ -获取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中,有一系统表
2、: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表示是 字段 可通过如下语句提取用户表的描述或列的描述，该描述全放在一个叫做syspro
3、perties的系统表中select sysobjects.name, sysproperties.Valuefrom sysproperties,sysobjectswhere sysproperties.id=sysobjects.id and sysproperties.name=MS_Description and sysproperties.type=3order by sysobjects.name在该表中，id 列与sysobjects中的id列是对应的，当该表的type值为时，是对表的描述，为，是对列的描述-1.获取所有数据库名:SELECT Name FROM Master.
4、SysDatabases ORDER BY Name-2.获取所有表名:SELECT Name FROM .SysObjects Where XType=U ORDER BY Name-XType=U:表示所有用户表;-XType=S:表示所有系统表;-3.获取所有字段名及说明:（Server2000）SELECT syscolumns.name,sysproperties.value AS CommentFROM sysproperties INNER JOIN sysobjects ON sysproperties.id = sysobjects.id INNER JOIN syscolu
5、mns ON sysobjects.id = syscolumns.id AND sysproperties.smallid = syscolumns.colidWHERE (sysproperties.type = 4) AND (sysobjects.name = message) -获取表中字段信息(主外键，字段名，数据类型，字段长度，列说明)select(case when PKeyCol.COLUMN_NAME is null then else PK end) +(case when KeyCol2.COLUMN_NAME is null then when NOT PKeyCol
6、.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 else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50) end) as 字段长度,-(case when coldesc.value is null then else coldesc.value end) AS 字段说明,*ISNULL (CAST(coldes
7、c.value AS nvarchar(50) , ) AS 字段说明from INFORMATION_SCHEMA.COLUMNS as col LEFT OUTER JOIN (select COLUMN_NAME,TABLE_NAME FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND KeyCol.
8、CONSTRAINT_NAME=RefCol.CONSTRAINT_NAMEWHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyColON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME AND PKeyCol.TABLE_NAME=Col.TABLE_NAME LEFT OUTER JOIN (INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2 INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON KeyCol2.CONSTR
9、AINT_CATALOG=RefCol2.CONSTRAINT_CATALOG AND KeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME AND KeyCol2.TABLE_NAME=Col.TABLE_NAME LEFT OUTER JOIN :fn_listextendedproperty (NULL, user, dbo, table, +tableName+, column, default) as coldesc ON col.COLUMN_NAME = col
10、desc.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 K
11、eyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME WHERE RefCol.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
12、 then else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50) end) as FieldLength,ISNULL (CAST(coldesc.value AS nvarchar(50) , ) AS FieldExplain from INFORMATION_SCHEMA.COLUMNS as col LEFT OUTER JOIN :fn_listextendedproperty (NULL, user, dbo, table, + tableName +, column, default) as coldesc ON col.COLUMN
13、_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME= + tableName + -获取数据库文件信息：select * from sys.database_files -可以使用内置的存储过程sp_MShelpcolumns查询表的结构。 如查询表B_RWZL的结构： sp_MShelpcolumns dbo.B_RWZL =（1）SELECT表名=case when a.colorder=1 then d.name else end,表说明=case when a.colorder=1 then isnull(f.value,) else end,字段序号=a.colorder,字段名=a.name,标识=case when COLUMNPROPERTY( a.id,a.name,IsIdentity)=1 t
