sql语句查询表的字段名

来源：互联网发布：java list indexof 编辑：程序博客网时间：2024/05/01 16:06

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '相应表名')
用以上sql语句输入相应表名就可以查到表的字段名，对应好数据库查询是否存在该表语句

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_cost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb_cost]
GO

创表语句

CREATE TABLE [dbo].[tb_cost] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[inputtime] [datetime] NOT NULL ,
[pushcount] [int] NOT NULL ,
[revertcount] [int] NOT NULL ,
[revertrate] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[unitprice] [float] NOT NULL ,
[cost] [float] NOT NULL ,
[income] [float] NOT NULL ,
[rate] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[pushinfor] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

建索引脚本：
create clustered index 索引名 on 表名(表.字段)

在表增加一个字段，例如fa字段

alter table 表名 add fa int not null default 0

选择前面 m-n 条数据的语句

例如选择前面第 5-10（m=10，n=5）条记录的sql语句如下：

sql="select top 6 * from table where (id not in (select top 4 id from table order by id desc)) order by id desc"

其中6，4 是这样得来的

sql="select top m-n+1 * from table where (id not in (select top n-1 id from table))