多表生成方法
declare @sqlstr nvarchar(4000)
declare @table nvarchar(50)
declare @i int
set @i = 1000
while @i<3000
begin
set @table = 'user_'+convert(nvarchar,@i)
set @sqlstr = '
CREATE TABLE [dbo].['+@table+'](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL DEFAULT (0),
[name] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL DEFAULT (''''),
[gsid] [int] NULL DEFAULT (0),
[type] [int] NULL DEFAULT (1),
[counts] [int] NULL DEFAULT (1),
[submit_time] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_'+@table+'] ON [dbo].['+@table+']
(
[userid] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_'+@table+'_userid] ON [dbo].['+@table+']
(
[userid] ASC,
[gsid] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+@table+'] TO [db_aller]
'
Exec sp_ExecuteSql @sqlstr
set @i = @i + 1000
-- print @sqlstr
end
GO
1.注意COLLATE Chinese_PRC_CI_AS NULL DEFAULT ('''')declare @table nvarchar(50)
declare @i int
set @i = 1000
while @i<3000
begin
set @table = 'user_'+convert(nvarchar,@i)
set @sqlstr = '
CREATE TABLE [dbo].['+@table+'](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL DEFAULT (0),
[name] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL DEFAULT (''''),
[gsid] [int] NULL DEFAULT (0),
[type] [int] NULL DEFAULT (1),
[counts] [int] NULL DEFAULT (1),
[submit_time] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_'+@table+'] ON [dbo].['+@table+']
(
[userid] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_'+@table+'_userid] ON [dbo].['+@table+']
(
[userid] ASC,
[gsid] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+@table+'] TO [db_aller]
'
Exec sp_ExecuteSql @sqlstr
set @i = @i + 1000
-- print @sqlstr
end
GO
2.去掉中间所有的GO
3.增加数据表权限GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+@table+'] TO [db_aller]