根据月份动态来生成表名,其格式为ResultDetail20091101-20091130,用于保存一个月的数据。
且需要动态执行增加约束和索引,然后定义为存储过程定期在每个月的1号执行
DECLARE @Month_Start DATETIME,@Month_End DATETIME;
DECLARE @Str_Start VARCHAR(10),@Str_End VARCHAR(10);
DECLARE @Tb_name VARCHAR(50),@Sql VARCHAR(2000);
DECLARE @TB_name_prefix varchar(15);-- 设定月的开始日期、结束日期及表名
SET @Month_Start = CONVERT(char(6),getdate(),112) + '01';
SET @Month_End = CONVERT(CHAR(8),DATEADD(DAY,-1,CONVERT(CHAR(8),DATEADD(Month,1,GETDATE()),120)+'1'),112);
SET @Str_Start = CONVERT(CHAR(10),@Month_Start,112);
SET @Str_End = CONVERT(CHAR(10),@Month_End,112);
SET @Tb_name = convert(varchar(8),@Str_Start,112)+'-'+convert(varchar(8),@Str_End,112);
SET @TB_name_prefix = 'ResultDetail';
SET @Tb_name = @TB_name_prefix + @Tb_name ;
--PRINT @Tb_nameIF NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')
--DROP TABLE dbo.['+@tb_name+'];SET @Sql = '
CREATE TABLE dbo.['+@tb_name+']
(
[ResultID] [int] NULL,
[IP] [varchar](15) COLLATE Latin1_General_BIN NOT NULL,
[Action] [varchar](20) COLLATE Latin1_General_BIN NULL,
[Host] [varchar](80) COLLATE Latin1_General_BIN NOT NULL,
[URL] [varchar](2100) COLLATE Latin1_General_BIN NOT NULL,
[Response] [int] NOT NULL,
[LoadTime] [int] NULL,
[StartTime] [int] NULL,
[BytesOut] [bigint] NULL,
[BytesIn] [bigint] NULL,
[CookieSize] [int] NULL,
[Expiration] [datetime] NULL,
[CacheControl] [varchar](40) COLLATE Latin1_General_BIN NULL,
[ContentType] [varchar](40) COLLATE Latin1_General_BIN NULL,
[ContentEncoding] [varchar](10) COLLATE Latin1_General_BIN NULL,
[ExecutionDate] [datetime] NULL,
[FirstPacketTime] [int] NULL,
[StartRenderTime] [int] NULL,
[DNSLookup] [int] NULL,
[Connections] [int] NULL,
[SSLtime] [int] NULL,
[TaskID] [int] NULL
) ON [PRIMARY]'
EXEC(@Sql)
--PRINT @SqlDECLARE @Ls_Sql VARCHAR(1000)
DECLARE @Al_Count INT--添加FOREIGN KEY
SELECT @Al_Count = Count(*)
FROM sysobjects
WHERE Name ='FK_ResultDetail_ResultID' + @Str_Start
IF @Al_Count = 0
BEGIN
SET @Ls_Sql = 'ALTER TABLE dbo.['+@tb_name+']
ADD CONSTRAINT [FK_ResultDetail_ResultID]+['+@tb_name+']
FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])'
EXEC(@Ls_Sql)
END--添加CHECK约束
SELECT @Al_Count = Count(*)
FROM sysobjects
WHERE Name ='CK_ResultDetail_ExecutionDate' + @Str_Start
IF @Al_Count = 0
BEGIN
SET @Ls_Sql = 'ALTER TABLE dbo.['+@tb_name+']
ADD CONSTRAINT CK_ResultDetail_ExecutionDate'+@tb_name+']
CHECK (ExecutionDate >= @Month_Start AND ExecutionDate < @Month_End)'
EXEC(@Ls_Sql)
END--添加索引
SELECT @Al_Count = Count(*)
FROM sysobjects AS S1
INNER JOIN sysindexes AS S2
ON S1.ID = S2.ID
WHERE S1.name = 'ResultDetail'+ @Str_Start
AND S2.name = 'IX_ResultDetail'+ @Str_Start
IF @Al_Count = 0
BEGIN
SET @Ls_Sql = 'CREATE CLUSTERED INDEX IX_ResultDetail['+@tb_name+']
ON dbo.['+@tb_name+'](ExecutionDate, Host, LoadTime)'
EXEC(@Ls_Sql)
ENDSELECT @Al_Count = Count(*)
FROM sysobjects AS S1
INNER JOIN sysindexes AS S2
ON S1.ID = S2.ID
WHERE S1.name = 'ResultDetail'+ @Str_Start
AND S2.name = 'IX_ResultDetail_ResultID'+ @Str_Start
IF @Al_Count = 0
BEGIN
SET @Ls_Sql = 'CREATE CLUSTERED INDEX IX_ResultDetail_ResultID['+@tb_name+']
ON dbo.['+@tb_name+'](ExecutionDate, Host, LoadTime)'
EXEC(@Ls_Sql)
END执行后提示的错误:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ResultDetail20091101-20091130'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ResultDetail20091101-20091130'.另:动态增加约束及索引时,该如何设定约束名和索引名,这个写法可能不对,[FK_ResultDetail_ResultID]+['+@tb_name+'],如果我要设定为 FK_ResultDetail_ResultID_ResultDetail20091101-20091130该如何设定?
小梁,小F在不在 ?
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')
--DROP TABLE dbo.['+@tb_name+'];DROP 语句这里报错,该如何处理,谢谢大家了
执行的时候报错,
我要将外键设定为 FK_ResultDetail_ResultID
加上 _ResultDetail20091101-20091130的组合,该如何设定?
这个可以直接修改啊
用alter语句
--DROP TABLE dbo.['+@tb_name+']; 也改为动态sql来执行另外,下面的语句,一部分一部分的注释,来查找错误。
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')
BEGIN
SET @Sql='DROP TABLE dbo.['+@tb_name+']'
EXEC(@Sql)
END
DECLARE @SQL_T varchar(150);
SET @SQL_T='DROP TABLE dbo.['+@tb_name+']'
EXEC (@SQL_T)
BEGIN
SET @Sql='DROP TABLE dbo.['+@tb_name+']'
EXEC(@Sql)
END
所就把那里改成 not exists
BEGIN
SET @Sql='DROP TABLE dbo.['+@tb_name+']'
EXEC(@Sql)
END
print出来的动态SQL 为
ALTER TABLE dbo.[ResultDetail20091101-20091130]
ADD CONSTRAINT FK_ResultDetail_ResultIDResultDetail20091101-20091130
FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])
但执行的时候又提示
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
ADD CONSTRAINT [FK_ResultDetail_ResultIDResultDetail20091101-20091130]
FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])