楼上多了个S,修改如下 if object_id('tablename') is nor null drop table tablenamecreate table tablename(......)
if object_id('tablename') is not null drop table tablenamecreate table tablename(......)
if object_id('tempdb..#tablename') is nor null drop table #tablenamecreate table #tablename ...
if exists (select * from sysobjects where id = object_id(@tabname) and OBJECTPROPERTY(id, N'IsUserTable') = 1) exec sp_executesql @deltab select @st='CREATE TABLE '+@tabname+ '( AnnounceID int identity (1, 1) NOT NULL , ParentID int default (0) NULL , Child int default (0) NULL , User_id int NULL , boardID int NULL , Topic nvarchar (255) NULL , Body ntext NULL , DateAndTime datetime default (getdate()) NULL , Hits int default (0) NULL , Length int default (0) NULL , RootID int default (0) NULL , Layer tinyint default (1) NULL , Orders int default (0) NULL , Ip nvarchar (20) default (0) NULL , Expression nvarchar (50) NULL , Forbid tinyint default(0) NULL )' exec sp_executesql @st
当然~, 先判断,若存在则。。 if object_id('tablename') is not null print '表已存在' 否则建立表 create table 表
--判断该表是否存在,若存在就删除 if exists (select * from dbo.sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [表名] --创建新表 CREATE TABLE [表名] ( 字段列表.... )
另外一个问题是:使用上面的方法,把检测过程做成存储过程,在存储过程中,如果创建TABLE? 这里的TABLE是通过调用存储过程时传入的SQL语句。换句话说,就是在存储过程中如何执行一个字符串化的SQL语句?Procedure CreateTable(@TableName,@TableString) If Object_ID('TempDB..'+@TableName) is not null Drop 'TempDB..'+@TableName Create Table @TableName @TableString End Procedure
Procedure CreateTable(@TableName,@TableString) If Object_ID('TempDB..'+@TableName) is not null Drop 'TempDB..'+@TableName exec('Create Table '+@TableName+@TableString) End Procedure
Procedure CreateTable(@TableName,@TableString) If Object_ID('TempDB..'+@TableName) is not null exec('Drop TempDB..'+@TableName) exec('Create Table '+@TableName+@TableString) End Procedure
create Proc CreateTable(@TableName varchar(100),@TableString varchar(1000)) as exec(' If Object_ID(''TempDB..'+@TableName+''') is not null Drop table TempDB..'+@TableName+' Create Table '+@TableName+' '+@TableString)
if object_id('tablename') is nor null
drop table tablenamecreate table tablename(......)
drop table tablenamecreate table tablename(......)
...
exec sp_executesql @deltab select @st='CREATE TABLE '+@tabname+
'(
AnnounceID int identity (1, 1) NOT NULL ,
ParentID int default (0) NULL ,
Child int default (0) NULL ,
User_id int NULL ,
boardID int NULL ,
Topic nvarchar (255) NULL ,
Body ntext NULL ,
DateAndTime datetime default (getdate()) NULL ,
Hits int default (0) NULL ,
Length int default (0) NULL ,
RootID int default (0) NULL ,
Layer tinyint default (1) NULL ,
Orders int default (0) NULL ,
Ip nvarchar (20) default (0) NULL ,
Expression nvarchar (50) NULL ,
Forbid tinyint default(0) NULL
)'
exec sp_executesql @st
先判断,若存在则。。
if object_id('tablename') is not null
print '表已存在'
否则建立表
create table 表
if exists (select * from dbo.sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [表名]
--创建新表
CREATE TABLE [表名] (
字段列表....
)
这里的TABLE是通过调用存储过程时传入的SQL语句。换句话说,就是在存储过程中如何执行一个字符串化的SQL语句?Procedure CreateTable(@TableName,@TableString)
If Object_ID('TempDB..'+@TableName) is not null
Drop 'TempDB..'+@TableName
Create Table @TableName @TableString
End Procedure
If Object_ID('TempDB..'+@TableName) is not null
Drop 'TempDB..'+@TableName exec('Create Table '+@TableName+@TableString)
End Procedure
If Object_ID('TempDB..'+@TableName) is not null
exec('Drop TempDB..'+@TableName) exec('Create Table '+@TableName+@TableString)
End Procedure
as
exec('
If Object_ID(''TempDB..'+@TableName+''') is not null
Drop table TempDB..'+@TableName+'
Create Table '+@TableName+' '+@TableString)