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

 

可以从数据库中的系统表 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	√