sql2000分享 批量建表dev_编号

批量建表dev_3970000000014到dev_3970000000035

declare @i bigint
declare @j int
declare @sql varchar(4000)
declare @sqlsource_delete varchar(4000)
declare @sqlsource_create varchar(4000)
declare @sn varchar(13)

set @sql = ''
set @i=3970000000013
set @sqlsource_delete = 'drop table [dbo].[dev_{SN}]'
set @sqlsource_create = 'CREATE TABLE [dbo].[dev_{SN}] 
(
[TasTimeStamp] [bigint] PRIMARY KEY ,
[DevTimeStamp] [bigint] NULL ,
[field_15] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[field_41] [real] NULL ,
[field_42] [real] NULL ,
[field_43] [real] NULL ,
[field_44] [real] NULL ,
[field_55] [nchar] (1) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]'

while @i<3970000000035
begin
set @i = @i + 1
set @sn = cast(@i as varchar(13))
set @j=len(@sn)

if exists (select * from dbo.sysobjects where id = object_id('[dbo].[dev_' + @sn + ']') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
begin
set @sql = replace(@sqlsource_delete, '{SN}', @sn)
execute(@sql)
end

set @sql = replace(@sqlsource_create, '{SN}', @sn)
execute(@sql)
end

 

 ！！！参考原文后重编 http://caozuiba.iteye.com/blog/1425950