查看数据库中表和列名数据类型

 

可以从数据库中的系统表 sysobjects 得到想要的数据表信息，具体SQL语句如下：

 

查看数据库中表名：

 

select name from sysobjects where type = 'U';

由于系统表sysobjects保存的都是数据库对象,其中type表示各种对象的类型，具体包括:

U = 用户表

S = 系统表

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

TF = 表函数

TR = 触发器

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程及相关的对象信息。

 

在sql server 2000 中加载驱动和URL路径的语句是

String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url = "jdbc:microsoft:sqlserver://localhost:1433; databasename=test";

而sql server 2005 中加载驱动和url的语句则为

String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://localhost:1433; databasename=test";

 

查询列名：

select name from syscolumns where id=object_id('dd_Stu') and objectproperty(id,'IsUserTable')=1; 

或者

sp_columns dd_Stu;

 

Java中查询表中字段、类型、长度等信息：

SELECT
字段名=a.Name,
类型=B.Name,
长度=COLUMNPROPERTY(a.ID,a.Name,'PRECISION'),
字段说明=IsNull(G.[value],'')
FROM syscolumns a
left join systypes B On a.Xtype=B.xusertype
left join sysproperties G On a.ID=G.ID And a.colid=G.smallid
Where a.id=object_id('要查询的表')    --如果只查询指定表,加上此条件
order by a.ID,a.colorder

 ASP.net中表的相关操作：

--删除主键，只有删除主键后才能删除主键对应的列
alter table worklog drop constraint PK_worklog

--创建主键
alter table worklog add constraint PK_worklog primary key(employeeNo,logNo)

--查询主键
select * from sysobjects where xtype='PK'

--创建外键，外键也必须制定字段 如，foreign key(employeeNo)
alter table worklog add constraint FK_worklog_employee foreign key(employeeNo) references employee(employeeNo)

--查询外键
select * from sysobjects where xtype='F'

--查询表
select * from sysobjects where xtype='U'
或者
select * from sysobjects where sysobjects.type='u'

name	id	xtype	uid	info	status	base_schema_ver	replinfo	parent_obj	crdate	ftcatid	schema_ver	stats_schema_ver	type	userstat	sysstat	indexdel	refdate	version	deltrig	instrig	updtrig	seltrig	category	cache
worklog	357576312	U	1	0	0	0	0	0	2014/1/21 12:09:19	0	0	0	U	1	3	0	2014/1/21 12:09:19	0	0	0	0	0	0	0

--查询表信息
sp_help employee

Name	Owner	Type	Created_datetime
employee	dbo	user table	2014/1/25 13:46:56

--查询字段信息表
select * from sys.extended_properties

class	class_desc	major_id	minor_id	name
1	OBJECT_OR_COLUMN	1365579903	0	microsoft_database_tools_support

select * from syscolumns --表字段信息表  

name	id	xtype	typestat	xusertype	length	xprec	xscale	colid	xoffset	bitpos	reserved	colstat	cdefault	domain	number	colorder	offset	collationid	language	status	type	usertype	printfmt	prec	scale	iscomputed	isoutparam	isnullable	collation
rowsetid	4	127	1	127	8	19	0	1	0	0	0	0	0	0	0	1	0	0	0	0	63	0		19	0	0	0	0
rowsetcolid	4	56	1	56	4	10	0	2	0	0	0	0	0	0	0	2	0	0	0	0	56	7		10	0	0	0	0

select * from sysobjects where sysobjects.type='s' --数据库所有的系统表的信息  
select * from syscomments --所有的触发器的东西  

--查询表名、字段序号、字段名、标识、主键、类型、占用字节数、长度、小数位数、允许空、默认值
SELECT   
(case when a.colorder=1 then d.name else '' end) as 表名,--如果表名相同就返回空  
     a.colorder as 字段序号,  
     a.name as 字段名,  
     (case when COLUMNPROPERTY( a.id,a.name, 'IsIdentity' )=1 then '√' else '' end) as 标识,  
     (case when (SELECT count(*) FROM sysobjects--查询主键  
                     WHERE (name in   
                             (SELECT name FROM sysindexes   
                             WHERE (id = a.id)   AND (indid in   
                                     (SELECT indid FROM sysindexkeys  
                                       WHERE (id = a.id) AND (colid in   
                                         (SELECT colid FROM syscolumns  
                                         WHERE (id = a.id) AND (name = a.name))  
                         )))))   
         AND (xtype = 'PK' ))>0 then '√' else '' end) as 主键,--查询主键END  
b.name as 类型,  
a.length as 占用字节数,  
COLUMNPROPERTY(a.id,a.name,'PRECISION' ) as    长度,  
isnull(COLUMNPROPERTY(a.id,a.name,'Scale' ),0) as 小数位数,  
(case when a.isnullable=1 then '√' else '' end) as 允许空,  
isnull(e.text,'' ) as 默认值,  
isnull(g.[value],'' ) AS 字段说明   
FROM syscolumns a left join systypes b   
on a.xtype=b.xusertype  
inner join sysobjects d   
on a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'   
left join syscomments e  
on a.cdefault=e.id  
left join sys.extended_properties g  
on a.id=g.major_id AND a.colid = g.minor_id   
     where d.name='employee' --所要查询的表  
order by a.id,a.colorder  

表名	字段序号	字段名	标识	主键	类型	占用字节数	长度	小数位数	允许空	默认值
employee	1	employeeNo		√	nvarchar	100	50	0
	2	employeeName			nchar	40	20	0	√
	3	employeePwd			nchar	40	20	0	√
	4	employeeEmail			nchar	80	40	0	√
	5	employeeAuthority			char	1	1	0	√
	6	employeePhone			nvarchar	22	11	0	√