sql server 2000中循环建立1024个表

admin

1. declare @i int
   
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(10)
   
7. 
   
8. set @sql = ''
   
9. set @i=1
   
10. set @sqlsource_delete = 'drop table [dbo].[Test_{SN}]'
    
11. set @sqlsource_create = 'CREATE TABLE [dbo].[Test_{SN}] (
    
12. [iid] [int] IDENTITY (1, 1) NOT NULL ,
    
13. [headid] [int] NULL ,
    
14. [istid] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
15. [istname] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
16. [isturl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
17. [ititle] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
18. [icontents] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
19. [icontent] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
    
20. [iurl] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
    
21. [ipic] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
    
22. [ipdate] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
23. [iedate] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
24. [icompname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
25. [icompemail] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
26. [iphone] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
27. [ifax] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
    
28. [ishengf] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
29. [iaddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
30. [ilianxiren] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
31. [imobile] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
32. [isite] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    
33. [isamecount] [int] NULL ,
    
34. [imainid] [int] NULL ,
    
35. [creationDate] [datetime] NOT NULL DEFAULT getdate() ,
    
36. [lastModifiedDate] [datetime] NOT NULL DEFAULT getdate()
    
37. CONSTRAINT [PK_Test_{SN}] PRIMARY KEY  CLUSTERED
    
38. (
    
39.   [iid]
    
40. )  ON [PRIMARY]  
    
41. ) ON [PRIMARY]'
    
42. 
    
43. while @i<=1024
    
44. begin
    
45.     set @sn = cast(@i as varchar(10))
    
46.     set @j=len(@sn)
    
47.    
    
48.     if exists (select * from dbo.sysobjects where id = object_id('[dbo].[Test_' + @sn + ']') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
    
49.     begin
    
50.         set @sql = replace(@sqlsource_delete, '{SN}', @sn)
    
51. --print @sql
    
52.         execute(@sql)
    
53.     end
    
54. 
    
55.     set @sql = replace(@sqlsource_create, '{SN}', @sn)
    
56.     --print @sql
    
57.     execute(@sql)
    
58. 
    
59.     set @i = @i + 1
    
60. end

复制代码