建立系統日志表的存儲過程(CreateSystemLogTable)

--  ============================================================== 
--  類型:    存儲過程 
--  名稱:    CreateSystemLogTable 
--  參數:    無 
--  作者:    Dicky 
--  日期:    2009-1-8 9:48 
--  描述:    檢查目前年中1-12月的系統日志表是否存在，不存在則建立， 
--             表名稱如System_log_20091...System_log_200912。 
--  ============================================================== 
CREATE   PROCEDURE   [ CreateSystemLogTable ]  
AS 
BEGIN 
     DECLARE   @tableName   NVARCHAR ( 50 )
     DECLARE   @createSql   NVARCHAR ( 500 )
     DECLARE   @i   INT 
     SET   @i   =   1 
     WHILE   @i   <=   12   /* 循環1-12個月 */ 
     BEGIN 
         SET   @tableName   =   ' System_log_ '   +   LTRIM ( YEAR ( GETDATE ()))  +   LTRIM ( @i )
         IF   NOT   EXISTS  ( SELECT   *   FROM  sysobjects  WHERE  id  =   OBJECT_ID (N ' [ '   +   @tableName   +   ' ] ' )  AND   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
             BEGIN 
                 SET   @createSql   =   ' 
                    CREATE TABLE [ '   +   @tableName   +   ' ] (
                        [id] [int] IDENTITY (1, 1) NOT NULL ,
                        [userid] [int] NULL ,
                        [type] [nvarchar] (50) NULL ,
                        [context] [nvarchar] (50) NULL ,
                        [ip] [nvarchar] (50) NULL ,
                        [createDate] [nvarchar] (50) NULL 
                    ) ON [PRIMARY]

                    ALTER TABLE [ '   +   @tableName   +   ' ] ADD 
                        CONSTRAINT [DF_ '   +   @tableName   +   ' _createDate] DEFAULT (GETDATE()) FOR [createDate] ' 
                 EXEC ( @createSql )
             END 
         SET   @i   =   @i   +   1 
     End 
END