下面是我创建一个数据表的存储过程,其中新表为复制其它一张表的结构并新增了一个自增字段autoCount,现在我想在创建这个存储过程的时候进行判断,如果表存在,则删除这张表,如果不存在则创建这个存储过程,请问该怎么解决!
CREATE PROCEDURE [dbo].[CreateTempTable]
@TableName varchar(100)
AS
declare @S nvarchar(4000)
set @S='
select identity(int,1,1) as Autocount,*into '+@TableName+' from dbo.JB_LiftInfo where UnitNo in(select UnitNo from dbo.JB_ContractUnit)'
exec (@S)
CREATE PROCEDURE [dbo].[CreateTempTable]
@TableName varchar(100)
AS
declare @S nvarchar(4000)
set @S='
select identity(int,1,1) as Autocount,*into '+@TableName+' from dbo.JB_LiftInfo where UnitNo in(select UnitNo from dbo.JB_ContractUnit)'
exec (@S)
drop table tb
AND type in (N'U')) --U 代表你查询的是表
IF(EXISTS(SELECT 1 FROM sysobjects WHERE xtype='u' AND name= '表名'))
SELECT '存在'
ELSE
SELECT '不存在'
@TableName varchar(100)
AS
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@TableName+']') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec('drop table '+@TableName);
declare @s varchar(4000)
set @s=
'select identity(int,1,1) as Autocount,*into '+@TableName+' from dbo.JB_LiftInfo where UnitNo in(select UnitNo from dbo.JB_ContractUnit)'
exec(@s)
end;GO