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]