mssql 2000 按月分区表(来于网络)

--创建

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'