--创建
create PROCEDURE SP_Records
@year nvarchar(4)
AS
declare @count int,
@yearmonth nvarchar(6),
@month nvarchar(2)
set @count = 0
while @count <12
begin
set @count = @count + 1
if @count <10 set @month = '0'+convert(nvarchar(1),@count)
else set @month = convert(nvarchar(2),@count)
set @yearmonth = @year+@month
if object_id ('..Records'+@yearmonth+'') is null
exec(
'CREATE TABLE Records'+@yearmonth+'(
-- [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ID] [varchar] (50) NOT NULL ,
[Rad_id] [int] NOT NULL ,
[IP] [varchar] (50) NOT NULL ,
[Addtime] [varchar] (20) NOT NULL
CHECK ([Addtime] BETWEEN '''+@year+'-'+@month+'-01 00:00:00'' AND '''+@year+'-'+@month+'-31 23:59:59''),
CONSTRAINT [PK_Records'+@yearmonth+'] PRIMARY KEY
(
[ID],
[Addtime]
) ON [PRIMARY]
) ON [PRIMARY]'
)
end
DECLARE @view varchar(8000)
set @view = ''
select @view = @view + ' UNION ALL SELECT * FROM [dbo].' + name
FROM sysobjects
where name like 'Records[0-9][0-9][0-9][0-9][0-1][0-9]'
and objectproperty(id,'isusertable')=1
set @view = stuff(@view, 1, 11, '')
if object_id('V_Records') is null
set @view = 'CREATE VIEW [V_Records] as ' + @view
else
set @view = 'alter VIEW [V_Records] as ' + @view
exec(@view)
print (@view)
GO
--执行
exec SP_Records @year='2009'