获取SQL_Server中的所有数据库用户,数据表,列,备注说明,主外键

获取SQL_Server中的所有数据库用户,数据表,列,备注说明,主外键获取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,sysobjectswhere sysproperties.id=sysobjects.idand =MS_Descriptionand sysproperties.type=3order by 在该表中，id 列与sysobjects中的id列是对应的，当该表的type值为时，是对表的描述，为，是对列的描述-1.获取所有数据库名:SELECT Name FROM Master.Sys.Databases 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.id INNER 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 else CAST(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 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.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.CONSTRAINT_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 = 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 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 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_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 else end,表说明=case when a.colorder=1 then isnull(f.value,) else end,字段序号=a.colorder,字段名=,标识=case when COLUMNPROPERTY( a.id,,IsIdentity)=1 then else end,主键=case when exists(SELECT 1 FROM sysobjects where xtype=PK and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid) then else end,类型=,占用字节数=a.length,长度=COLUMNPROPERTY(a.id,,PRECISION),小数位数=isnull(COLUMNPROPERTY(a.id,,Scale),0),允许空=case when a.isnullable=1 then else end,默认值=isnull(e.text,),字段说明=isnull(g.value,)FROM syscolumns aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype=U and dtpropertiesleft join syscomments e on a.cdefault=e.idleft join sysproperties g on a.id=g.id and a.colid=g.smallidleft join sysproperties f on d.id=f.id and f.smallid=0-where =要查询的表 -如果只查询指定表,加上此条件order by a.id,a.colorder（2）SQL2000系统表的应用-1：获取当前数据库中的所有用户表select Name from sysobjects where xtype=u and status=0-2：获取某一个表的所有字段select name from syscolumns where id=object_id(表名)-3：查看与某一个表相关的视图、存储过程、函数select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like %表名%-4：查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype=P-5：查询用户创建的所有数据库select * from master.sysdatabases D where sid not in(select sid from master.syslogins where name=sa)或者select dbid, name AS DB_NAME from master.sy