-- ============================================= -- Create table specify constraints -- ============================================= IF EXISTS (SELECT name FROM sysobjects WHERE name = N'<table_name, sysname, test_table>' AND type = 'U') DROP TABLE <table_name, sysname, test_table> GOCREATE TABLE <table_name, sysname, test_table> ( <column_1, sysname, c1> <datatype_for_column_1, , int> NOT NULL, <column_2, sysname, c2> <datatype_for_column_2, , int> NOT NULL CONSTRAINT <column_constraint, sysname, check_c2> CHECK(c2 > 0), CONSTRAINT <table_constraint, sysname, pk_c1> PRIMARY KEY (c1) ) GO
也可以从SYSOBJECTS表里取表名 IF NOT EXISTS(SELECT 1 FROM OBJECT WHERE NAME='TABLENAME' AND TYPE='U') 列名从SYSCOLUMNS里取
if not exists(select 1 from sysobjects where name='tablename' and xtype='u') begin --增加表的语句 end else begin if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tablename' and xtype='u' and a.name='colname') --增加表字段语句 end
怎么加了xtype不行,去了才行
我使用上述方法后,运行出现:Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'else'. Msg 102, Level 15, State 1, Line 12 Incorrect syntax near 'end'.
运行后,出现:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ')'.
if not exists(select 1 from sysobjects where name='tablename' and xtype='u') begin create table tablename(.....) end else begin if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tablename' and xtype='u' and a.name='colname') begin alter .... end end
主要是通过系统表dbo.sysobjects检查表是否存在 通过系统表dbo.syscolumns检查指定表的指定字段是否存储 语句用 if exists(select 1 from dbo.sysobjects where id=object_id('表名')) 判断是否存储表 if exists(select 1 from dbo.syscolumns where id=object_id('表名') and name='指定字段名')
use mpps_koufei If not exists (select 1 from sysobjects where name='tmpclientslog' and xtype='U')begin --如不存在tmpclientslog表,创建新表
CREATE TABLE [dbo].[tmpClientslog]( [id] [int] IDENTITY(1,1) NOT NULL, [cli_idt] [varchar](25) NOT NULL, [cli_joinbch] [varchar](20) NULL, [cli_paybch] [varchar](20) NULL, [cli_typeid] [char](4) NULL, [cli_joindate] [datetime] NULL, [cli_cpjoindate] [datetime] NULL, [cli_smsjoindate] [datetime] NULL, [cli_cpleavedate] [datetime] NULL, [cli_smsleavedate] [datetime] NULL, [LastDateStatus] [nvarchar](10) NULL, [update_dt] [datetime] NULL) --增加主键 alter table tmpclientslog add constraint PK_tmpclientslog primary key (id) endelse --如存在tmpclientslog表 begin --检查是否存在某字段 if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tmpclientslog' and b.xtype='u' and a.name='column1') --如不存在 column1 列,则增加该列 begin ALTER TABLE tmpclientslog ADD column1 VARCHAR(30) end else begin ALTER table tmpclientslog drop column column1
end end 前面的问题基本解决了,我想做到如果查得 tmpclientslog 表 出现column1,则删除该列,上面不能实现,请问是什么原因呢?
你仔细看看10楼的代码。。 我有抄袭你的么? 。。else 里面 既然判断了 if 后面多条就要加BGEIN END
use mpps_koufei If not exists (select 1 from sysobjects where name='tmpclientslog' and xtype='U') begin --如不存在tmpclientslog表,创建新表 CREATE TABLE [dbo].[tmpClientslog]( [id] [int] IDENTITY(1,1) NOT NULL, [cli_idt] [varchar](25) NOT NULL, [cli_joinbch] [varchar](20) NULL, [cli_paybch] [varchar](20) NULL, [cli_typeid] [char](4) NULL, [cli_joindate] [datetime] NULL, [cli_cpjoindate] [datetime] NULL, [cli_smsjoindate] [datetime] NULL, [cli_cpleavedate] [datetime] NULL, [cli_smsleavedate] [datetime] NULL, [LastDateStatus] [nvarchar](10) NULL, [update_dt] [datetime] NULL) --增加主键 alter table tmpclientslog add constraint PK_tmpclientslog primary key (id) end else --如存在tmpclientslog表 begin --检查是否存在某字段 if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tmpclientslog' and b.xtype='u' and a.name='column1') --如不存在 column1 列,则增加该列 begin ALTER TABLE tmpclientslog ADD column1 VARCHAR(30) end else begin ALTER table tmpclientslog drop column column1
end end
楼主你用的是10楼的方法吧? if not exists(select 1 from sysobjects where name='tablename' and xtype='u') begin create table tablename(.....) end else begin if exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tablename' and xtype='u' and a.name='colname') begin alter table tmpclientslog drop column column1 end else begin alter table tmpclientslog add 。 end end
-- Create table specify constraints
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<table_name, sysname, test_table>'
AND type = 'U')
DROP TABLE <table_name, sysname, test_table>
GOCREATE TABLE <table_name, sysname, test_table> (
<column_1, sysname, c1> <datatype_for_column_1, , int> NOT NULL,
<column_2, sysname, c2> <datatype_for_column_2, , int> NOT NULL
CONSTRAINT <column_constraint, sysname, check_c2> CHECK(c2 > 0),
CONSTRAINT <table_constraint, sysname, pk_c1> PRIMARY KEY (c1) )
GO
IF NOT EXISTS(SELECT 1 FROM OBJECT WHERE NAME='TABLENAME' AND TYPE='U')
列名从SYSCOLUMNS里取
begin
--增加表的语句
end
else
begin
if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tablename' and xtype='u' and a.name='colname')
--增加表字段语句
end
我使用上述方法后,运行出现:Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'else'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'end'.
运行后,出现:Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
begin
create table tablename(.....)
end
else
begin
if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tablename' and xtype='u' and a.name='colname')
begin
alter ....
end
end
通过系统表dbo.syscolumns检查指定表的指定字段是否存储
语句用
if exists(select 1 from dbo.sysobjects where id=object_id('表名')) 判断是否存储表
if exists(select 1 from dbo.syscolumns where id=object_id('表名') and name='指定字段名')
If not exists (select 1 from sysobjects where name='tmpclientslog' and xtype='U')begin --如不存在tmpclientslog表,创建新表
CREATE TABLE [dbo].[tmpClientslog](
[id] [int] IDENTITY(1,1) NOT NULL,
[cli_idt] [varchar](25) NOT NULL,
[cli_joinbch] [varchar](20) NULL,
[cli_paybch] [varchar](20) NULL,
[cli_typeid] [char](4) NULL,
[cli_joindate] [datetime] NULL,
[cli_cpjoindate] [datetime] NULL,
[cli_smsjoindate] [datetime] NULL,
[cli_cpleavedate] [datetime] NULL,
[cli_smsleavedate] [datetime] NULL,
[LastDateStatus] [nvarchar](10) NULL,
[update_dt] [datetime] NULL) --增加主键
alter table tmpclientslog add constraint PK_tmpclientslog primary key (id) endelse --如存在tmpclientslog表
begin
--检查是否存在某字段
if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tmpclientslog' and b.xtype='u' and a.name='column1')
--如不存在 column1 列,则增加该列
begin
ALTER TABLE tmpclientslog ADD column1 VARCHAR(30)
end else
begin
ALTER table tmpclientslog drop column column1
end
end 前面的问题基本解决了,我想做到如果查得 tmpclientslog 表 出现column1,则删除该列,上面不能实现,请问是什么原因呢?
我有抄袭你的么?
。。else 里面
既然判断了 if
后面多条就要加BGEIN END
If not exists (select 1 from sysobjects where name='tmpclientslog' and xtype='U') begin --如不存在tmpclientslog表,创建新表 CREATE TABLE [dbo].[tmpClientslog](
[id] [int] IDENTITY(1,1) NOT NULL,
[cli_idt] [varchar](25) NOT NULL,
[cli_joinbch] [varchar](20) NULL,
[cli_paybch] [varchar](20) NULL,
[cli_typeid] [char](4) NULL,
[cli_joindate] [datetime] NULL,
[cli_cpjoindate] [datetime] NULL,
[cli_smsjoindate] [datetime] NULL,
[cli_cpleavedate] [datetime] NULL,
[cli_smsleavedate] [datetime] NULL,
[LastDateStatus] [nvarchar](10) NULL,
[update_dt] [datetime] NULL) --增加主键
alter table tmpclientslog add constraint PK_tmpclientslog primary key (id) end else --如存在tmpclientslog表
begin
--检查是否存在某字段
if not exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tmpclientslog' and b.xtype='u' and a.name='column1')
--如不存在 column1 列,则增加该列
begin
ALTER TABLE tmpclientslog ADD column1 VARCHAR(30)
end else
begin
ALTER table tmpclientslog drop column column1
end
end
楼主你用的是10楼的方法吧?
if not exists(select 1 from sysobjects where name='tablename' and xtype='u')
begin
create table tablename(.....)
end
else
begin
if exists(select 1 from syscolumns a,sysobjects b where a.id=b.id and b.name='tablename' and xtype='u' and a.name='colname')
begin
alter table tmpclientslog drop column column1
end
else
begin
alter table tmpclientslog add 。
end
end
今天抄了一个我的还不够,没完啊你,自觉点吧。
把我的代码拿去,把我注释的地方加上你的 create ,alter 就成你自己写的了?
表名、别名、字段名完全一样。写的怎么那么像呢?完全拿来主义!
尊重别人的劳动是最基本的美德,亏你还是在校大学生呢
也太死板了吧?