drop proc proc_CheckEQCurrentDataTableExists
go
--建表过程，先判断表有没有存在，确定要建表，则建表
create proc proc_CheckEQCurrentDataTableExists(@tablename varchar(100),@createTable bit ,@tbexit bit out,@doresult int out )
as
declare @createsql varchar(2000)
set @doresult=0
if exists (select top 1 * from sysobjects where id = object_id('EQInitialData.dbo.'+@tablename ) and xtype='U')
set @tbexit = 1
else
set @tbexit=0
if (@tbexit=0 and @createTable=1)
begin
set @createsql = '
CREATE TABLE [dbo].['+ @tablename+'](
[dataid] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Sim] [varchar](12) NULL,
[Commandword] [char](4) NULL,
[clientBeiJinDatetime] [datetime] NULL,
[Valid] [char](1) NULL,
[Latitude] [decimal](18, 4) NULL,
[LatitudeFlag] [char](1) NULL,
[Longitude] [decimal](18, 4) NULL,
[LongitudeFlag] [char](1) NULL,
[Speed] [decimal](18, 2) NULL,
[GreenwichTime] [datetime] NULL,
[Orientation] [decimal](5, 1) NULL,
[IOState] [char](8) NULL,
[Mileage] [decimal](18, 2) NULL,
[platTime] [datetime] NULL,
CONSTRAINT [PK_'+@tablename+'] PRIMARY KEY CLUSTERED
(
[dataid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
'
exec(@createsql)
set @doresult =@@error
end
--创建365张表
go
declare @i int
declare @tablename varchar(50)
declare @tbexit bit;
declare @result bit;
declare @datename datetime
declare @errorMsg nvarchar(4000)
set @datename =DATEADD(MM,-1, GETDATE())--提前一个月
set @i = 1
while(@i<=365)
begin
set @datename = DATEADD(D,1,@datename);
set @tablename = 'EQCurrentData'+CONVERT(varchar(12) , @datename, 112 );
exec proc_CheckEQCurrentDataTableExists @tablename,1, @tbexit out, @result out
set @i = @i +1
if (@result0)
begin
set @errorMsg = @errorMsg + '创建表：' + @tablename + '失败；'
end
end
--显示建过程中的错误
select @errorMsg
