sql2000分享 批量建表dev_编号

批量建表dev_3970000000014到dev_3970000000035

 1 declare @i bigint
 2 declare @j int
 3 declare @sql varchar(4000)
 4 declare @sqlsource_delete varchar(4000)
 5 declare @sqlsource_create varchar(4000)
 6 declare @sn varchar(13)
 7 
 8 set @sql = ''
 9 set @i=3970000000013
10 set @sqlsource_delete = 'drop table [dbo].[dev_{SN}]'
11 set @sqlsource_create = 'CREATE TABLE [dbo].[dev_{SN}] 
12 (
13 [TasTimeStamp] [bigint] PRIMARY KEY ,
14 [DevTimeStamp] [bigint] NULL ,
15 [field_15] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
16 [field_41] [real] NULL ,
17 [field_42] [real] NULL ,
18 [field_43] [real] NULL ,
19 [field_44] [real] NULL ,
20 [field_55] [nchar] (1) COLLATE Chinese_PRC_CI_AS NULL 
21 ) ON [PRIMARY]'
22 
23 while @i<3970000000035
24 begin
25 set @i = @i + 1
26 set @sn = cast(@i as varchar(13))
27 set @j=len(@sn)
28 
29 if exists (select * from dbo.sysobjects where id = object_id('[dbo].[dev_' + @sn + ']') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
30 begin
31 set @sql = replace(@sqlsource_delete, '{SN}', @sn)
32 execute(@sql)
33 end
34 
35 set @sql = replace(@sqlsource_create, '{SN}', @sn)
36 execute(@sql)
37 end

 

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

posted @ 2016-11-25 18:26  十年点滴  阅读(372)  评论(0)    收藏  举报