sql server 2000中循环建立1024个表

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

set @sql = ''
set @i=1
set @sqlsource_delete = 'drop table [dbo].[Test_{SN}]'
set @sqlsource_create = 'CREATE TABLE [dbo].[Test_{SN}] (
[iid] [int] IDENTITY (1, 1) NOT NULL ,
[headid] [int] NULL ,
[istid] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[istname] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[isturl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ititle] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[icontents] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[icontent] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[iurl] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[ipic] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[ipdate] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[iedate] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[icompname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[icompemail] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[iphone] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ifax] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[ishengf] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[iaddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ilianxiren] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[imobile] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[isite] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[isamecount] [int] NULL ,
[imainid] [int] NULL ,
[creationDate] [datetime] NOT NULL DEFAULT getdate() ,
[lastModifiedDate] [datetime] NOT NULL DEFAULT getdate() 
CONSTRAINT [PK_Test_{SN}] PRIMARY KEY CLUSTERED 
(
[iid]
) ON [PRIMARY] 
) ON [PRIMARY]'

while @i<=1024
begin
set @sn = cast(@i as varchar(10))
set @j=len(@sn)

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

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

set @i = @i + 1
end

以上转自http://caozuiba.iteye.com/blog/1425950

自己改的添加dev_3970000000001至dev_3970000005000表：

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=3970000000001
set @sqlsource_delete = 'drop table [dbo].[dev_{SN}]'
set @sqlsource_create = 'CREATE TABLE [dbo].[dev_{SN}] 
(
 [TasTimeStamp] [bigint] IDENTITY (1, 1) NOT NULL ,
 [DevTimeStamp] [bigint] NULL ,
 [field_15] [varchar] (50) default null ,
 [field_41] [real] NULL ,
 [field_42] [real] NULL ,
 [field_43] [real] NULL ,
 [field_44] [real] NULL ,
 [field_55] [nchar] (1) NULL  
 CONSTRAINT [PK_dev_{SN}] PRIMARY KEY  CLUSTERED 
 (
  [TasTimeStamp]
 )  ON [PRIMARY]  
) ON [PRIMARY]'


while @i<=3970000005000
begin
    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)
 --print @sql
        execute(@sql)
    end


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


    set @i = @i + 1
end