Sql批量建表、删表，表名以数字命名，且固定长度

由于项目需要，需要一次性建5000张表，且每张表的命名为Target0001-Target5000：

代码如下：

/*下面循环创建有误，第一行 '4' 附近有错误*/declare @ii intDECLARE @length INTset @ii = 1SET @length = 4/*将数字转换为固定长度的字符串，不够的以前导码0填充×/while @ii <= 6000begindeclare @strSql varchar(500)declare @_str varchar(10)set @_str=REPLICATE('0',@length-DATALENGTH(CONVERT(VARCHAR,@ii)))+CONVERT(VARCHAR,@ii)/*set @_str = ltrim(@ii)*/set @strSql = 'create table Target'+ @_str + '(Vol float,Gas float,Temp float,Speed float,Alarm int,X float,Y float,Z float,wTime datetime)'print (@strSQl)set @ii = @ii + 1execute(@strSql)end

 删除表
declare @ii int  
DECLARE @length INT  
set @ii = 1  
SET @length = 4
while @ii <= 6000
begin  
declare @strSql varchar(500)  
declare @_str varchar(10)  
set @_str=REPLICATE('0',@length-DATALENGTH(CONVERT(VARCHAR,@ii)))+CONVERT(VARCHAR,@ii)  
/*set @_str = ltrim(@ii)*/  
set @strSql = 'DROP table Target'+ @_str
print (@strSQl)  
set @ii = @ii + 1  
execute(@strSql)  
end

批量建带有主键的表，并且主键ID自增的sql

declare @ii int  
DECLARE @length INT  
set @ii = 2  
SET @length = 4
while @ii <= 2000  
begin  
declare @strSql varchar(500)  
declare @_str varchar(10)  
set @_str=REPLICATE('0',@length-DATALENGTH(CONVERT(VARCHAR,@ii)))+CONVERT(VARCHAR,@ii)  
/*set @_str = ltrim(@ii)*/  
set @strSql = 'create table Target'+@_str +'(ID int identity(1,1) not null,TPSY_groupName nvarchar(100),TPSY_areaName nvarchar(100),TPSY_analyserIp varchar(100),TPSY_photoType int,TPSY_photoPath nvarchar(100),TPSY_creatTime datetime,TPSY_storageTime datetime,TPSY_photoName nvarchar(100) PRIMARY KEY '+
'( '+
' [ID] '+
')  ON [PRIMARY])'
print (@strSQl)  
set @ii = @ii + 1  
execute(@strSql)  
end